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:

1. Connect to the publication server
2. Execute sp_scriptpublicationcustomprocs ‘publication’ , replace the publication name to match it
3. The above execution has generated some scripts including the one listed above
4. Now, I connect to the subscriber/peer with Dedicated Admin Connection aka DAC
5. Change the context to the replicated database
6. Executed the part of the script/missing procedure
7. Wait for some time and observe how replication is progressing
8. Most probably it will throw one more similar error, and it tells me some more missing stored procedure names
9. As I already have scripted it, I copied them and executed it onto a subscriber/peer
10. Monitored the replication and yes, it started processing smooth, all good
11. 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)