May 7, 2010 at 7:52 am
We have a customer who is receiving an error on their Distribution Agent stating:
Could not find stored procedure 'sp_MSins_<table_name>'
The problem is that the 'sp_MSins_<table_name>' Stored Procedure does exist on the subscription database.
Here is some background. Our product relies on Transactional Replication, although we do not initialize the subscription database automatically. Instead we create our own replication procedures (i.e. the Insert, Update and Delete procedures) and bcp the data from the publication database and then Bulk Insert into the subscription database. This all works fine and has been replicating using transactional replication for years.
A problem occurred recently because the customer attempted to re-initialise the subscription database by simply right clicking on the subscription and selecting "Reinitialize". Subsequently the replication's Distribution Agent failed with errors stating:
The process could not bulk copy into table...
This happened for several of the tables it was attempting to initialize.
This wasn't too much of a problem though. I checked the earliest transactions that were in the distribution.dbo.MSrepl_commands table and found that 2 transactions (with many commands) relating to attempts to reinitialise, such as "sync" and "SYNCSTAT". I deleted these from the distribution.dbo.MSrepl_commands table and restarted the Distribution Agent, so that the next transactions that were waiting to be replicated could be processed.
The current transaction that is waiting to be replicated is now a simple insert into a table, which will use one of the sp_MSins_[tablename] procedures. It should be straight forward. The sp_MSins_[tablename] procedures all exist on the subscription database. However, we are now getting an error stating:
Could not find stored procedure 'sp_MSins_<table_name>'
I have checked everything I can think of. The permissions are fine. The Distribution Agent is running under a Sys Admin account. The Stored Procedure exists on the subscription database. I have profiled the Distribution Agent and can see it attempting to execute the sp_MSins Stored Procedure on the correct database using the correct login, but it fails.
If I copy the execution of the sp_MSins Stored Procedure from the trace and run this directly on the subscription database while logged on as the login that the Distribution Agent is using, it is successful (albeit that I put this inside a transaction that I then rollback).
Any help on this would be most appreciated.
May 27, 2010 at 4:30 pm
We're having a very similar issue, except it is on a new setup of Replication.
May 28, 2010 at 3:50 am
I thought I'd post an update on what the problem actually was in case it is useful to anyone else.
Regarding the initial problem re:
Could not find stored procedure 'sp_MSins_<table_name>'
The distribution.dbo.MSrepl_commands table actually had two records for this command in the same transaction for the same insert. There was a slight difference in these however.
The first was normal in that the command began with:
[font="Courier New"]{CALL [sp_MSins_<table_name>][/font]
The type of this command was 30.
The second, however, began with:
[font="Courier New"]{CALL [sp_MSins_<table_name>];2[/font]
The type of this command was -2147483618.
The problem was in trying to execute the 2nd version of the sp_MSins_<table_name>, i.e. the one that had the ;2 appended to it.
When I checked the distribution.dbo.MSrepl_commands table it contained a mix of the following types:
typecount
-21474836181,796
-214748361078
301,994,733
As can be seen the bulk of the commands that had been waiting to be replicated over the period that replication was down were of type 30.
When I checked all of the commands of type -2147483618 and -2147483610 they were all attempting to execute 2nd versions of replication procedures (i.e. with the ;2 appended to them). Also, these all belonged to a 1 or 2 hour period around the time when I believe the attempted re-initialise took place.
When I checked the previous commands that had been failing with the "sync" and "SYNCSTAT" commands, theie type's were all in the region of -2147483646 to -2147483578.
I searched the internet and couldn't find any explanation of what the different types referred to, but I think it is reasonable to assume that type 30 relates to normal transactional replication, while the -2147483618 (and others in that region) types are related to an attempted reinitialisation.
I deleted the commands of type -2147483618 and -2147483610 from the distribution.dbo.MSrepl_commands table and restarted replication and it started successfully. Another few minor errors occurred due to PK violations, but I dealt with those and continued to restart synchronization. After that I left it running and it caught up later the same night.
June 7, 2010 at 4:15 pm
Chris,
I actually found the exact same issue using the sp_browsereplcmds. However, when I deleted the duplicate commands with type -2147483618 some (but not all) of the affected publications popping up with errors where subsequent transactions reference rows that do not exist on the subscriber. We could potentially bring these rows over manually, but at this point I'm concerned with the integrity of the replication.
May 17, 2012 at 6:42 am
Hi,
Is there some way I could undo this reinitialization without deleting the data in the tables? Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply