Replication: Help! I lost a replication system stored procedure

  • Article

“Could not find stored procedure MSMERGE_INS/UPD/DEL_SP_XXXXXXXXXX”

If you have replication set up on a SQL Server 2005 (any build post SP2) or SQL 2008, and you receive a similar error as the one mentioned above, there is a way to resolve this issue without having to reinitialize / drop - create your publication.

https://msdn.microsoft.com/en-us/library/bb326615.aspx

SP_VUPGRADE_MERGEOBJECTS

Regenerates the article-specific triggers, stored procedures, and views that are used to track and apply data changes for merge replication. Execute this procedure in the following situations:

  • If an object required by replication is accidentally dropped.
  • If you apply an update, such as a hotfix, that requires modification to one or more replication objects. Execute the procedure on each node after applying the update.

Executing this stored procedure does not require reinitialization of subscriptions. This procedure is not required if you install a service pack or upgrade to a new version of SQL Server.

NOTE:

Please do not take this stored procedure for granted – system stored procedures should not be altered / tweaked / touched.

NOTE II:

You must stop all the synchronization activities both at the publisher and at the subscriber. Once that is done, you can run the sp_vupgrade_mergeobjects stored procedure against the database.