By: Atul Gaikwad | Updated: 2017-10-05 | Comments (4) | Related: > 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'”.
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:
About the author
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.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-10-05
