Cannot drop the procedure 'dbo.aux_CheckStatistics', because it does not exist or you do not have permission.

Products

• IT Management Suite

Issue/Introduction

During an upgrade via SIM (Symantec Installation Manager), the process fails while trying to configure as seen in these log entries:

Error while importing SQL object: aux_CheckStatistics
Cannot drop the procedure 'dbo.aux_CheckStatistics', because it does not exist or you do not have permission.

Message 1:

Symantec.Installation.ConfigureNS.StartCurrentTask: starting configuration task Configuring database....
-----------------------------------------------------------------------------------------------------
Date: 1/5/2017 9:55:55 AM, Tick Count: 7918407 (02:11:58.4070000), Size: 391 B
Process: SymantecInstallationManager (8352), Thread ID: 28, Module: SymantecInstallationManager.exe
Priority: 4, Source: Symantec.Installation.ConfigureNS.StartCurrentTask

 

Message 2:

Error while importing SQL object: aux_CheckStatistics
Cannot drop the procedure 'dbo.aux_CheckStatistics', because it does not exist or you do not have permission.
   [System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Altiris.NS.DataAccessLayer.DatabaseAbilities.ExecuteNonQuery(String sql)
   at Altiris.NS.ContextManagement.AdminDatabaseContext.<>c__DisplayClass1`1.<PerformTransactedDeadlockRetry>b__0(IDatabaseContext ctx)
   at Altiris.NS.ContextManagement.AdminDatabaseContext.PerformTransactedDeadlockRetry(Action`1 action, String deadlockMessage, String category)
   at Altiris.NS.ItemManagement.ItemHelper.ImportSqlNodes(XmlNodeList nodes, Boolean installing, Boolean resetConnection)
   at Altiris.NS.ItemManagement.ItemHelper.ImportSqlObjectNodes(XmlNodeList nodes)

SQL Exception details: code=3701, line=1

Exception logged from:
   at Symantec.Installation.Logging.LogActivity.ReportException(Int32 severity, String strMessage, String category, Exception exception, String footer)
   at Symantec.Installation.Logging.LogActivity.ReportException(Int32 severity, String strMessage, String category, Exception exception)
   at Altiris.NS.ItemManagement.ItemHelper.ImportSqlObjectNodes(XmlNodeList nodes)
   at Altiris.NS.ItemManagement.ItemHelper.ImportSql(XmlNode itemNode, Boolean inner)
   at Symantec.Installation.NSConfiguration.Tasks.InstallSQLTask.InstallSQL()
   at Symantec.Installation.NSConfiguration.Tasks.InstallSQLTask.StartAsync()
   at Symantec.Installation.ConfigureNS.StartCurrentTask()
   at Symantec.Installation.ConfigureNS.task_Completed(Object sender, CompletedEventArgs e)
   at Symantec.Installation.NSConfiguration.Tasks.InstallSQLTask.StartAsync()
   at Symantec.Installation.ConfigureNS.StartCurrentTask()
   at Symantec.Installation.ConfigureNS.task_Completed(Object sender, CompletedEventArgs e)
   at Symantec.Installation.NSConfiguration.Tasks.InstallSQLTask.StartAsync()
   at Symantec.Installation.ConfigureNS.StartCurrentTask()
   at Symantec.Installation.ConfigureNS.task_Completed(Object sender, CompletedEventArgs e)
   at Symantec.Installation.NSConfiguration.Tasks.ConfigurationServicesTask.StartAsync()
   at Symantec.Installation.ConfigureNS.StartCurrentTask()
   at Symantec.Installation.ConfigureNS.task_Completed(Object sender, CompletedEventArgs e)
   at Symantec.Installation.ReportingTask.EndAsync(IAsyncResult result)
   at System.Web.Services.Protocols.WebClientAsyncResult.Complete()
   at System.Web.Services.Protocols.WebClientProtocol.ProcessAsyncResponseStreamResult(WebClientAsyncResult client, IAsyncResult asyncResult)
   at System.Web.Services.Protocols.WebClientProtocol.ReadAsyncResponseStream(WebClientAsyncResult client)
   at System.Web.Services.Protocols.WebClientProtocol.ReadAsyncResponse(WebClientAsyncResult client)
   at System.Web.Services.Protocols.WebClientProtocol.GetResponseAsyncCallback(IAsyncResult asyncResult)
   at System.Net.LazyAsyncResult.Complete(IntPtr userToken)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Net.ContextAwareResult.Complete(IntPtr userToken)
   at System.Net.HttpWebRequest.ProcessResponse()
   at System.Net.HttpWebRequest.SetResponse(CoreResponseData coreResponseData)
   at System.Net.ConnectionReturnResult.SetResponses(ConnectionReturnResult returnResult)
   at System.Net.Connection.ReadComplete(Int32 bytesRead, WebExceptionStatus errorStatus)
   at System.Net.Connection.ReadCallback(IAsyncResult asyncResult)
   at System.Net.LazyAsyncResult.Complete(IntPtr userToken)
   at System.Net.ContextAwareResult.Complete(IntPtr userToken)
   at System.Net.Sockets.BaseOverlappedAsyncResult.CompletionPortCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* nativeOverlapped)
   at System.Threading._IOCompletionCallback.PerformIOCompletionCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* pOVERLAP)

-----------------------------------------------------------------------------------------------------
Date: 1/5/2017 9:55:55 AM, Tick Count: 7918501 (02:11:58.5010000),  Size: 5.31 KB
Process: SymantecInstallationManager (8352), Thread ID: 28, Module: SymantecInstallationManager.exe
Priority: 1, Source: Altiris.NS.ItemManagement.ItemHelper.ImportSqlObjectNodes

 

Message 3:

ConfigureNS - task_Completed(): Configuration Task Configuring database... Failed: Database context invalidated by inner exception
Database context invalidated by inner exception
   [Altiris.Database.InvalidDatabaseContextException @ Symantec.Installation.PlatformInterface]
   at Altiris.Database.DatabaseContext`1.ProcessException(Exception exception)
   at Altiris.NS.DataAccessLayer.DatabaseAbilities.ExecuteNonQuery(String sql)
   at Altiris.NS.ContextManagement.AdminDatabaseContext.<>c__DisplayClass1`1.<PerformTransactedDeadlockRetry>b__0(IDatabaseContext ctx)
   at Altiris.NS.ContextManagement.AdminDatabaseContext.PerformTransactedDeadlockRetry(Action`1 action, String deadlockMessage, String category)
   at Altiris.NS.ItemManagement.ItemHelper.ImportSqlNodes(XmlNodeList nodes, Boolean installing, Boolean resetConnection)
   at Altiris.NS.ItemManagement.ItemHelper.ImportSqlNodes(XmlNodeList nodes)
   at Altiris.NS.ItemManagement.ItemHelper.ImportSql(XmlNode itemNode, Boolean inner)
   at Symantec.Installation.NSConfiguration.Tasks.InstallSQLTask.InstallSQL()
   at Symantec.Installation.NSConfiguration.Tasks.InstallSQLTask.StartAsync()

There is already an object named 'aux_CheckStatistics' in the database.
   [System.Data.SqlClient.SqlException @ .Net SqlClient Data Provider]
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Altiris.NS.DataAccessLayer.DatabaseAbilities.ExecuteNonQuery(String sql)

SQL Exception details: code=2714, line=1, procedure=aux_CheckStatistics

Exception logged from:
   at Symantec.Installation.Logging.LogActivity.ReportException(Int32 severity, String strMessage, String category, Exception exception, String footer)
   at Symantec.Installation.Logging.LogActivity.ReportException(String strMessage, String category, Exception exception)
   at Symantec.Installation.ConfigureNS.task_Completed(Object sender, CompletedEventArgs e)
   at Symantec.Installation.NSConfiguration.Tasks.InstallSQLTask.StartAsync()
   at Symantec.Installation.ConfigureNS.StartCurrentTask()
   at Symantec.Installation.ConfigureNS.task_Completed(Object sender, CompletedEventArgs e)
   at Symantec.Installation.NSConfiguration.Tasks.InstallSQLTask.StartAsync()
   at Symantec.Installation.ConfigureNS.StartCurrentTask()
   at Symantec.Installation.ConfigureNS.task_Completed(Object sender, CompletedEventArgs e)
   at Symantec.Installation.NSConfiguration.Tasks.InstallSQLTask.StartAsync()
   at Symantec.Installation.ConfigureNS.StartCurrentTask()
   at Symantec.Installation.ConfigureNS.task_Completed(Object sender, CompletedEventArgs e)
   at Symantec.Installation.NSConfiguration.Tasks.ConfigurationServicesTask.StartAsync()
   at Symantec.Installation.ConfigureNS.StartCurrentTask()
   at Symantec.Installation.ConfigureNS.task_Completed(Object sender, CompletedEventArgs e)
   at Symantec.Installation.ReportingTask.EndAsync(IAsyncResult result)
   at System.Web.Services.Protocols.WebClientAsyncResult.Complete()
   at System.Web.Services.Protocols.WebClientProtocol.ProcessAsyncResponseStreamResult(WebClientAsyncResult client, IAsyncResult asyncResult)
   at System.Web.Services.Protocols.WebClientProtocol.ReadAsyncResponseStream(WebClientAsyncResult client)
   at System.Web.Services.Protocols.WebClientProtocol.ReadAsyncResponse(WebClientAsyncResult client)
   at System.Web.Services.Protocols.WebClientProtocol.GetResponseAsyncCallback(IAsyncResult asyncResult)
   at System.Net.LazyAsyncResult.Complete(IntPtr userToken)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Net.ContextAwareResult.Complete(IntPtr userToken)
   at System.Net.HttpWebRequest.ProcessResponse()
   at System.Net.HttpWebRequest.SetResponse(CoreResponseData coreResponseData)
   at System.Net.ConnectionReturnResult.SetResponses(ConnectionReturnResult returnResult)
   at System.Net.Connection.ReadComplete(Int32 bytesRead, WebExceptionStatus errorStatus)
   at System.Net.Connection.ReadCallback(IAsyncResult asyncResult)
   at System.Net.LazyAsyncResult.Complete(IntPtr userToken)
   at System.Net.ContextAwareResult.Complete(IntPtr userToken)
   at System.Net.Sockets.BaseOverlappedAsyncResult.CompletionPortCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* nativeOverlapped)
   at System.Threading._IOCompletionCallback.PerformIOCompletionCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* pOVERLAP)

-----------------------------------------------------------------------------------------------------
Date: 1/5/2017 9:56:04 AM, Tick Count: 7927393 (02:12:07.3930000), Size: 5.73 KB
Process: SymantecInstallationManager (8352), Thread ID: 28, Module: SymantecInstallationManager.exe
Priority: 1, Source: Symantec.Installation.ConfigureNS.task_Completed

Environment

ITMS 8.x

Cause

Missing DBO permission on the database for the account (AppID) used during the upgrade process.

This proc is dropped and created under the NS_Update.config and called by some Task Management and Directory Connector config files. This is what the config file is trying to do:

<sqlObjects>

    <sqlObject name="aux_CheckStatistics">

      <sqlStatement exists="true"><![CDATA[

                      drop proc dbo.aux_CheckStatistics

                ]]></sqlStatement>

      <sqlStatement exists="both"><![CDATA[

                      CREATE PROC aux_CheckStatistics

                (

                      @TabName nvarchar(128),

                      @ColName nvarchar(128)

                )

                AS

                BEGIN

                      DECLARE @id int

                      SELECT  @id = OBJECT_ID( @TabName )

                      IF ( @id IS NOT NULL )

                      BEGIN

                            DECLARE @StatNameTable TABLE ( Name nvarchar(128) )

                            INSERT INTO @StatNameTable

                                  SELECT    st.name

                                        FROM  sys.columns       cl

                                        JOIN  sys.stats_columns sc ON sc.object_id = cl.object_id AND sc.column_id = cl.column_id

                                        JOIN  sys.stats         st ON st.object_id = sc.object_id AND st.stats_id = sc.stats_id

                                        WHERE cl.object_id = @id

                                        AND   cl.name = @ColName

                                        AND   st.auto_created = 0 and st.user_created = 1

                            IF @@ROWCOUNT > 0

                            BEGIN

                                  DECLARE @Query nvarchar(max)

                                  SELECT  @Query = 'DROP STATISTICS '

                                  SELECT  @Query = @Query + @TabName + '.' + QUOTENAME(Name) + ',' FROM @StatNameTable

                                  SELECT  @Query = SUBSTRING( @Query, 0, LEN(@Query) )

                                  EXEC  ( @Query )

                                  RETURN 1

                            END

                      END

                      RETURN 0

                END

                ]]></sqlStatement>

    </sqlObject>

  </sqlObjects>

 

Resolution

Grant DBO rights to the AppID (Application Identify) for the Symantec_CMDB database. Then retry the upgrade process from SIM again.