Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have SQL Server 2008 R2 Merge replication set up.

We have 20+ push subscriptions.

Once in a while I get the following error message during the sync:

Error messages: The merge process could not set the status of the subscription correctly.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200963)
Could not find stored procedure 'dbo.sp_MScheckIsPubOfSub'.
(Source: MSSQLServer, Error number: 2812)

I have found the code for the mentioned SP & ran it against the subscriber database & everything worked fine.

My question is: why is this stored procedure getting deleted every now and then by itself ? And how I can troubleshoot this issue?

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

Please check for orphaned rows for deleted subscriptions in sysmergesubscriptions and delete them. For example, the subscriber that this is failing on may have duplicate entries in sysmergesubscriptions, one being orphaned from a previously deleted subscription.

This has been the cause of this error and solution for clients of mine in the past, ymmv. I hope this helps.

share|improve this answer
    
Another option would be to delete and recreate the subscription for this subscriber, starting from a fresh database at the subscriber. –  Brandon Williams Nov 10 '12 at 20:34
    
I did the cleanup and the next sync completed successfully, but the sysmergesubscriptions table was refilled again with the orphaned rows!! Is there a way to delete these orphaned rows permanently? –  Adel Khayata Nov 11 '12 at 3:36
    
They might get cleaned up after they expire. If not, you may need to drop and recreate the subscription. –  Brandon Williams Nov 11 '12 at 18:27
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.