SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Distribution Agent Error re:Could not find stored procedure 'sp_MSins_<table_name>'


Distribution Agent Error re:Could not find stored procedure 'sp_MSins_<table_name>'

Author
Message
Chris O'Donnell
Chris O'Donnell
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 10
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.
Dave Slee
Dave Slee
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 124
We're having a very similar issue, except it is on a new setup of Replication.
Chris O'Donnell
Chris O'Donnell
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 10
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:
{CALL [sp_MSins_<table_name>]
The type of this command was 30.


The second, however, began with:
{CALL [sp_MSins_<table_name>];2
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:

type count
-2147483618 1,796
-2147483610 78
30 1,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.
Dave Slee
Dave Slee
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 124
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.
Vinnie123
Vinnie123
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 48
Hi,
Is there some way I could undo this reinitialization without deleting the data in the tables? Thank you.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search