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'”.
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.
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.
Next Steps
- Let me know if you face the similar kind of error while setting up replication in SQL Server 2014 or other versions by placing a comment in the section below.
- Check out these tips:
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 handling small to large SQL Server production support projects. He is technology savvy and believes in automation and innovation. He likes to automate anything and everything which can help save time and make a DBAs life effortless and smooth, to focus on larger projects. He has extensive hands on experience in SQL Server administration and high availability solutions like clustering, replication, log shipping, mirroring, etc. He also possesses strong knowledge of SSRS and SSIS.
- MSSQLTips Awards: Rookie of the Year Contender – 2016
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?
Awesome, perfectly worked for me.
Very Helpful Thank You!