Resolving could not find stored procedure in replication

I was fine Sunday, I was tired enough and feeling asleep, my eyes were burning and I was feeling hard to concentrate, during that time my pager rang. The page was a request from a customer who has their Peer-2-Peer (P2P) replication broken. When I read replication is to be fixed, I was like no!!! But it was really a quick fix while I get the error details, it says could not find stored procedure in replication.

Let us quickly look at what exactly an error looks like and how did I fix it.

Error: could not find stored procedure in replication sp_MSIns_Table or sp_MSDel

What does this error mean?

This means that few system stored procedures related to replication are missing, and because of that, the replication could not proceed further. It will look something like below:

could not find stored procedure 'sp_MSins_dboSSCReplDemo945929061'.

Why did I see this error?

I will try to answer it for you, according to my understanding, when a snapshot was created update schema option was not chosen. And then, if an underlying schema change this error will surface.

Resolving could not find stored procedure in replication

What you will need to do is as below:

Connect to the publication server
Execute sp_scriptpublicationcustomprocs ‘publication’ , replace the publication name to match it
The above execution has generated some scripts including the one listed above
Now, I connect to the subscriber/peer with Dedicated Admin Connection aka DAC
Change the context to the replicated database
Executed the part of the script/missing procedure
Wait for some time and observe how replication is progressing
Most probably it will throw one more similar error, and it tells me some more missing stored procedure names
As I already have scripted it, I copied them and executed it onto a subscriber/peer
Monitored the replication and yes, it started processing smooth, all good
This is how I resolve replication error could not find stored procedure

PS: In case you try to execute those system stroed procedure with the normal connection you will see an error like below. And, just in case if you like read some more tips related to replication follow this link

Msg 126, Level 15, State 1, Procedure ssp_MSins_dboSSCReplDemo945929061, Line 17 Invalid pseudocolumn "$sys_p2p_cd_id".

photo credit: wuestenigel Werkzeug-Set: Zange & Schraubenzieher via photopin (license)

Hemantgiri Goswami

Throughout my extensive 24-year tenure in the IT industry, I have honed my expertise in SQL Server and cloud technologies. My qualifications include certifications in ITIL, Azure, and Google Cloud, and my professional journey boasts a consistent record of delivering top-notch, dependable, and efficient solutions across diverse clients and domains. In recognition of my dedication and impact, I am honored to have received the Microsoft MVP award for SQL Server on six occasions. Additionally, I actively contribute to various online forums and blogs, acting as a moderator and facilitator of meaningful discussions. My ultimate mission revolves around empowering organizations to enhance the reliability and efficiency of their SQL Server implementations while fostering a culture of continuous learning and growth within the SQL Server community.