Commerce Server Upgrade Wizard fails with the Invalid object name "sysprocesses" error


Description

If you upgrade Commerce Server from an earlier version to 11.3 or later, you can get errors similar to the following in the Upgrade Wizard:

The log file can contain entries such as:

<Product Catalog>: Info: Beginning product catalog schema migration
<Product Catalog>: Info: Product catalog schema migration failed, however, the changes to full text catalogs cannot be rolled back.
<Product Catalog>: Error: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'sysprocesses'.
   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 CommerceServer.Core.Internal.ServerUtility.ExecuteSqlScriptFromString(SqlConnection connection, SqlTransaction transaction, String sqlScriptContents)
   at CommerceServer.Core.Internal.ServerUtility.ExecuteSqlScript(SqlConnection connection, SqlTransaction transaction, String sqlFileName)
   at CommerceServer.Core.Migration.CatalogComponent.RunMigrationSchema(CatalogExecutionContext executionContext, String catalogCreateSqlPath)
   at CommerceServer.Core.Migration.CatalogComponent.Migrate()
ClientConnectionId:9cc4600d-649c-44a1-b3ac-10c09d6cad36
Error Number:208,State:1,Class:16

Solution

The error is caused by a DB schema change to the dbo.ctlg_DropPersistentTablesInScratchDB stored procedure, which uses a one-part identifier for the sysprocesses object. This causes errors in older versions of the SQL Server (such as SQL 2008) because the 3-part identifier format ([database_name].[schema_name].object_name) is expected.

To fix the issue, you can apply the following changes to the CatalogCreate.sql file (its default install location is C:\Program Files (x86)\Commerce Server 11).

There are three occurrences of the following statement inside the creation of the dbo.ctlg_DropPersistentTablesInScratchDB procedure (the line starts with CREATE PROCEDURE dbo.ctlg_DropPersistentTablesInScratchDB).

Change the three occurrences of this statement:

Select '*' From sysprocesses Where  spid = @Tablespid_tmp

To:

Select '*' From master..sysprocesses Where  spid = @Tablespid_tmp

Save the changes to the CatalogCreate.sql file and run the Upgrade Wizard again.