We encountered problems while applying Service Packs and when attaching an older version of a database to a newer version SQL Server instance (requiring the database upgrade process to run).
Upgrading subscription settings and system objects in database [DataWarehouse].
Invalid object name 'MSreplication_subscriptions'.
Error executing sp_vupgrade_replication.
Saving upgrade script status to 'SOFTWARE\Microsoft\MSSQLServer\Replication\Setup'.
Saved upgrade script status successfully.
Within the sp_vupgrade_replication procedure a subprocedure sp_vupgrade_subscription_databases is called which in turn calls another procedure sp_vupgrade_subscription_tables. It is in this last procedure you will find the following snippet of code:
IF EXISTS ( SELECT * FROM sys.objects WHERE name = 'MSreplication_subscriptions' ) or
EXISTS ( SELECT * FROM sys.objects WHERE name = 'MSsubscription_agents' )
IF EXISTS ( SELECT publication, publisher_db, publisher, subscription_type
GROUP BY publication, publisher_db, publisher, subscription_type
HAVING COUNT(*) > 1 )
RAISERROR (21203, 10, 1, @table_name)
IF NOT EXISTS ( SELECT * FROM sysindexes WHERE name = 'uc1MSReplication_subscriptions' AND
id = OBJECT_ID('MSreplication_subscriptions') )
CREATE UNIQUE CLUSTERED INDEX uc1MSReplication_subscriptions ON
MSreplication_subscriptions(publication, publisher_db, publisher, subscription_type)
Clearly it is checking for the existence of certain objects; however, it does not distinguish between actual tables and synonyms. In our case synonyms had been created in the database that pointed to the tables in another database that was a subscriber in a replication topology. This included synonyms that pointed at the various replication tables. And therefore there were named objects in sys.objects that incorrectly met the criteria in this internal T-SQL code. While this is a developer mistake on our part it should not cause the upgrade process to fail.