Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Resolve SQL Server Replication Error - Could not find stored procedure sp_MSupd_dboPS


By:   |   Read Comments   |   Related Tips: More > Replication

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

In our SQL Server replication architecture, we have a SQL Server 2014 publication on one instance and we have a SQL Server 2014 subscriber on another instance. The subscription was initialized from backup. The subscriber fails to synchronize, giving the following error from the distribution agent: Could not find stored procedure 'sp_Msupd_dboPS'.

Solution

We were setting up transactional replication for SQL Server 2014 and a subscription was initialized from backup because the database we setup was very large.  We estimated that initializing the subscription from a snapshot might have taken more than 8 hours.  For setting up replication from a backup refer to this tip Initialize SQL Server replication using a database backup.

Transactional Replication Error – Missing Stored Procedure

After we setup replication we found replication was failing with the below error: “Could not find stored procedure 'sp_Msupd_dboPS'”.

SQL Server Replication error: Could not find stored procedure sp_Msupd_dboPS

As the error message states, the root cause for the issue is that the stored procedures for replication are not present in the subscriber database.

Script to Generate Missing SQL Server Replication Stored Procedure

After further investigation, we found that there is system stored procedure, which we will have to run against the publication in order to generate a script for the missing stored procedure that is required for transactional replication setup on a subscriber.

On the publisher, we ran the stored procedure sp_scriptpublicationcustomprocs. This generated the scripts for the replication stored procedures.  We used the script output to create the stored procedures on the subscriber database.

Script to Generate Missing SQL Server Replication Stored Procedure

The stored procedure sp_scriptpublicationcustomprocs scripts the custom INSERT, UPDATE, and DELETE procedures for all table articles in a publication in which the auto-generate custom procedure schema option is enabled.  For more detail refer to this Microsoft article - sp_scriptpublicationcustomprocs.

After we ran the script on the subscriber, the replication error was resolved and the subscriber synched with the publisher.

No SQL Server replication issues
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Atul Gaikwad Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools