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:

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.

 

This proc is dropped and created under 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>

 

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.0, upgrading from 7.6 HF7

Cause

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

Resolution

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