Merge Replication " Error 2812: Could not find stored procedure 'dbo.sp_MScheckIsPubOfSub' "

  • Article

The SQL Server Replication errors are very unique and hard to troubleshoot. 

Depending on the Replication architecture you have or the SQL Server versions, you can encounter different issues that you have to troubleshoot , every time from scratch.

One of the error messages that have a specific reproduction scenario is  "Error 2812 : Could not find stored procedure 'dbo.sp_MScheckIsPubOfSub'".

You  can find below the steps to reproduce the issue and some recommendations to workaround or to prevent the error message from happening.

Steps to Reproduce:

Step1

We have the following configuration : Merge Replication  with the Publisher and the Distributor on the same server and 3 Subscribers with different build of SQL Server.

1 Publisher (on the same server we have also the Distributor) with SQL Server 2008R2  SP2

1 Subscriber with SQL Server 2008R2  RTM that we named  SUB0

1 Subscriber with SQL Server 2008R2 SP2 that we named SUB1

1 Subscriber with SQL Server 2008R2 RTM that we named SUB2

 

Step2

We make sure the replication is working fine . We test the synchronization. We verify if all is ok in the Replication Monitor on Publisher.

 

Step3

We will turn off manually the Subscriber SUB0.

We will now delete the Subscriber SUB0 using the interface: right click on the subscriber delete. During the delete we will receive a popup with the message indicating that the subscriber was successfully deleted from the Publisher but it could not connect to the subscriber and delete is from there.

 

Step4

We try to restart all remaining replications : SUB1 and SUB2.

The synchronization:

- For SUB1 is done with success because the SQL Server version for this subscriber is SQL Server 2008 R2 SP2 and the stored procedure exists;

- For SUB2 it fails with the error message described above;

 

Recommendations:

This stored procedure (sp_MScheckIsPubOfSub) will only get called when the following conditions are true:

1. Either publication side or subscription side, there is an entry in the sysmergesubscriptions table whose status is 2.  (status 2 means deleted)

2. The information in the publication side and subscription side is not the same, which means, for the same entry,  the status for the publication side does not equal to the one in the subscription side.

When the above conditions are true, then the stored  procedure sp_MScheckIsPubOfSub will be called to check whether the subscriber belongs to the publisher.

However, why this kind of meta data mismatch happened?

In normal situation, when you delete a subscription, the status on the both sides will be updated to 2 by system itself. A very possible cause was when you were deleting some subscription, some temporary network issue happened,which caused that the publication (for push subscription) cannot connect to the subscription side to do the update. And then this kind of metadata mismatch happened.

As such, in order to prevent it from happening again:

1. Please ensure that the stored procedure is created on Publisher and Subscriber side and it’s marked as system object.

2. While you are deleting some subscriptions, please ensure that the corresponding subscription and publication are all online, and accessible. This will ensure that the metadata is consistent.

 

Hopefully the details and recommendations helped in better understanding why the error message appeared and also what can we do to prevent it from happening again.