Service Revision error when updating to 4.0 SP13

When upgrading from your current SP to 4.0 SP 13, you may come across the following service revision error, 
The "RunServiceRevisions" task failed unexpectedly.
Blackbaud.AppFx.Platform.BuildTasks.RunServiceRevisionsHelpers.RevisionExecutionException: An error occurred while executing a service revision. The index 'AK_BUSINESSPROCESSSTATUS' is dependent on column 'STATUS'.
ALTER TABLE DROP COLUMN STATUS failed because one or more objects access this column.
Revision text: 
alter table dbo.[BUSINESSPROCESSSTATUS] drop column [STATUS];

alter table dbo.[BUSINESSPROCESSSTATUS]
  add [STATUS] as (case when [STATUSCODE]=(0) AND [NUMBEROFEXCEPTIONS]=(0) then N'Completed' when [STATUSCODE]=(0) AND [NUMBEROFEXCEPTIONS]>(0) then N'Completed with exceptions' when [STATUSCODE]=(1) then N'Running' when [STATUSCODE]=(2) then N'Did not finish' when [STATUSCODE]=(3) then N'Results cleared' when [STATUSCODE]=(4) then N'Enqueued' end);

exec dbo.[USP_SCHEMA_TABLE_SETCOLUMNCOMMENT] 'BUSINESSPROCESSSTATUS', 'STATUS', 'Provides a translation for the ''STATUSCODE'' field.';

       ---> System.Data.SqlClient.SqlException: The index 'AK_BUSINESSPROCESSSTATUS' is dependent on column 'STATUS'.
ALTER TABLE DROP COLUMN STATUS 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.SqlInternalConnection.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 Blackbaud.AppFx.Platform.BuildTasks.RunServiceRevisionsHelpers.RevisionRunner.RunSQLRevision(String sql, Boolean useATransaction)
   at Blackbaud.AppFx.Platform.BuildTasks.RunServiceRevisionsHelpers.RevisionRunner.RunRevs(DBRevisions revs, Int32 startAtRevisionID, Boolean onlyLoadNonDesignerSpecs)
   --- End of inner exception stack trace ---
   at Blackbaud.AppFx.Platform.BuildTasks.RunServiceRevisionsHelpers.RevisionRunner.RunRevs(DBRevisions revs, Int32 startAtRevisionID, Boolean onlyLoadNonDesignerSpecs)
   at Blackbaud.AppFx.Platform.BuildTasks.RunServiceRevisionsHelpers.RevisionRunner.RunLatestRevisions()
   at Blackbaud.AppFx.Platform.BuildTasks.RunServiceRevisions.Execute()
   at Microsoft.Build.BuildEngine.TaskEngine.ExecuteInstantiatedTask(EngineProxy engineProxy, ItemBucket bucket, TaskExecutionMode howToExecuteTask, ITask task, Boolean& taskResult)
Target "RunServiceRevisions" failed.
This could suggest the client have placed their own indexes on this table which are causing the problem. This can be checked by looking at the preface, for example AK, for the index.  Our (Blackbaud) revisions are failing because that index has a dependency that is preventing the installer from completing the correct action on this table.  It should be a relatively simple matter of temporarily removing or disabling their index while revisions run, then creating it after the run. 

Environment

 Blackbaud CRM
 4.0
 4.0
 4.0.166.0

Was this article helpful?