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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Replication » Distribution Agent Error re:Could not find...

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

Rate Topic

Display Mode

Topic Options

Author

Message

Chris O'Donnell

Posted Friday, May 07, 2010 7:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 21, 2010 8:19 AM
Points: 2, 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.

Post #917971

Dave Slee

Posted Thursday, May 27, 2010 4:30 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 10:53 AM
Points: 134, Visits: 104

We're having a very similar issue, except it is on a new setup of Replication.

Post #929361

Chris O'Donnell

Posted Friday, May 28, 2010 3:50 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 21, 2010 8:19 AM
Points: 2, 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.

Post #929552

Dave Slee

Posted Monday, June 07, 2010 4:15 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 10:53 AM
Points: 134, Visits: 104

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.

Post #933721

Vinnie123

Posted Thursday, May 17, 2012 6:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 9:00 AM
Points: 2, Visits: 41

Hi,
Is there some way I could undo this reinitialization without deleting the data in the tables? Thank you.

Post #1301705

« Prev Topic | Next Topic »

Permissions