000038581 - RSA Archer upgrade fails for Database component due to custom SQL Index

Document created by RSA Customer Support Employee on Mar 26, 2020
Version 1Show Document
  • View in full screen mode

Article Content

Article Number000038581
Applies ToRSA Product Set: Archer
RSA Version/Condition: All
 
IssueArcher installer fails to complete successfully throwing the below error:

User-added image

When you check the FrameWorkInstall log file, more details about the error are logged as below:
 

Set log directory C:\Program Files\RSA Archer\
Updating .Net Framework Registry Key
Updating database
Loading database script resources
SetupUtils.Database.SqlException: An error occurred at step 1 of script 6.6.00100.1012.sql (length=586, max=0) ---> System.Data.SqlClient.SqlException: The index 'ixn_tblAsyncJobQueue_Running' is dependent on column 'Running'.
ALTER TABLE DROP COLUMN Running failed because one or more objects access this column.
   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& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SetupUtils.Database.ScriptExecutor.ExecuteCommand(String scriptName, String cmdText, Int32 step)
   --- End of inner exception stack trace ---
   at Archer.Installer.Pages.PerformInstallationPage.InstallDatabase(Operation dbOp, String connectionString, String instanceName, Boolean allowSkipOnError)
   at Archer.Installer.Pages.DBOperationHandler.Execute()
   at Archer.Installer.Pages.PerformInstallationPage.OnWizardNext(WizardPageEventArgs e)
The full sql statement follows:
/***** D:\GitDev\Archer\ce-develop\DB\ScriptsModel\Changing_the_computation_formula_for_Running_column_of_tblAsyncJobQueue.sql *****/
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS where column_name = 'Running'
        and table_name =N'tblAsyncJobQueue')
BEGIN

    ALTER TABLE tblAsyncJobQueue DROP COLUMN Running


END

IF not EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS where column_name = 'Running'
        and table_name =N'tblAsyncJobQueue')
BEGIN
ALTER TABLE tblAsyncJobQueue
ADD  Running  AS (Case When [process_id] > 0 Then 1 Else 0 end)

END


Inner exception:
System.Data.SqlClient.SqlException (0x80131904): The index 'ixn_tblAsyncJobQueue_Running' is dependent on column 'Running'.
ALTER TABLE DROP COLUMN Running failed because one or more objects access this column.
   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& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SetupUtils.Database.ScriptExecutor.ExecuteCommand(String scriptName, String cmdText, Int32 step)
ClientConnectionId:8862ac22-06a8-465e-8866-c1fa3bc5424d
Error Number:5074,State:1,Class:16
SetupUtils.Database.SqlException: An error occurred at step 1 of script 6.6.00100.1012.sql (length=586, max=0) ---> System.Data.SqlClient.SqlException: The index 'ixn_tblAsyncJobQueue_Running' is dependent on column 'Running'.
ALTER TABLE DROP COLUMN Running failed because one or more objects access this column.
   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& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SetupUtils.Database.ScriptExecutor.ExecuteCommand(String scriptName, String cmdText, Int32 step)
   --- End of inner exception stack trace ---
   at Archer.Installer.Pages.PerformInstallationPage.InstallDatabase(Operation dbOp, String connectionString, String instanceName, Boolean allowSkipOnError)
   at Archer.Installer.Pages.DBOperationHandler.Execute()
   at Archer.Installer.Pages.PerformInstallationPage.OnWizardNext(WizardPageEventArgs e)
The full sql statement follows:
/***** D:\GitDev\Archer\ce-develop\DB\ScriptsModel\Changing_the_computation_formula_for_Running_column_of_tblAsyncJobQueue.sql *****/
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS where column_name = 'Running'
        and table_name =N'tblAsyncJobQueue')
BEGIN

    ALTER TABLE tblAsyncJobQueue DROP COLUMN Running


END

IF not EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS where column_name = 'Running'
        and table_name =N'tblAsyncJobQueue')
BEGIN
ALTER TABLE tblAsyncJobQueue
ADD  Running  AS (Case When [process_id] > 0 Then 1 Else 0 end)

END


Inner exception:
System.Data.SqlClient.SqlException (0x80131904): The index 'ixn_tblAsyncJobQueue_Running' is dependent on column 'Running'.
ALTER TABLE DROP COLUMN Running failed because one or more objects access this column.
   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& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at SetupUtils.Database.ScriptExecutor.ExecuteCommand(String scriptName, String cmdText, Int32 step)
ClientConnectionId:8862ac22-06a8-465e-8866-c1fa3bc5424d
Error Number:5074,State:1,Class:16
CauseA custom SQL Index was created in the Archer database preventing the Archer upgrade from completing successfully.
Resolution
  1. Take note of the custom SQL index name in the error.  For example, in the error above, the SQL index name is "ixn_tblAsyncJobQueue_Running".
  2. Restore the SQL database from backup taken before upgrade attempt.  This is needed because the installer did not complete upgrade properly and is not smart enough to pick up where it left off.
  3. Locate the table with the custom SQL Index. 
  4. Delete the custom SQL Index.
  5. Run the Archer installer again. 
  6. If another custom SQL Index causes upgrade error, repeat steps 1-5 again.

User-added image
NotesRun the Archer Configuration Report for the Instance to get the list of SQL indexes… See the SQL Fragmentation and SQL Index Stats sections. 
NOTE: The sections were added in newer Archer versions.

Attachments

    Outcomes