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


By:   |   Updated: 2017-10-05   |   Comments (4)   |   Related: More > Replication


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





get scripts

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


Article Last Updated: 2017-10-05

Comments For This Article




Saturday, July 17, 2021 - 1:26:28 PM - Nagesh Back To Top (89026)
Thanks for the nice article, can you please clarify on do we need to execute whole output or just only one sp that is erroring out?

Wednesday, February 17, 2021 - 1:09:58 PM - Praveen Back To Top (88255)
Awesome, perfectly worked for me.

Thursday, January 14, 2021 - 1:27:17 PM - Guest Back To Top (88043)
Very Helpful Thank You!

Saturday, July 13, 2019 - 1:55:44 AM - Kishore Back To Top (81736)

It helped me lot and svaed my valuable time.. thank you..

--Kishore.



download














get free sql tips
agree to terms