I am recreating (push) transactional replication from a SQL 2000 instance to a SQL 2008 instance which I had to break in order to do a db migration. The 2008 instance is both the distributor and a subscriber.

About 1/2 of my subscriptions are failing with

Could not find stored procedure 'sp_MSins_dboTABLE'.

(more info):

Command attempted: if @@trancount > 0 rollback tran (Transaction sequence number: 0x0002A2B400008D19000100000000, Command ID: 20)

Error messages: Could not find stored procedure 'sp_MSins_dboTABLE'. (Source: MSSQLServer, Error number: 2812) Get help: Could not find stored procedure 'sp_MSins_dboTABLE'. (Source: MSSQLServer, Error number: 2812) Get help: I've chased just about every path I can find, from running sp_scriptpublicationcustomprocs (I executed the result in the distribution database) to deleting the subscription, then the publication, and then re-creating them.

The working subscriptions were created using the same methodology.

Here are my create scripts:



Update --- My failing subscriptions are also throwing the following error (through Profiler):

Table already has a primary key defined on it.

  • Which database did you migrate, the publisher or the subscriber? – mrdenny Jul 23 '12 at 16:47
  • Migrated the publisher – swasheck Jul 23 '12 at 18:27

It sounds like you just need to reinitialize the subscription which will recreate all the objects on the subscriber.

EDIT (Added by asker):

After re-initialization, if there are errors to the effect that it couldn't find a CRUD stored procedure, fire up SQL Profiler and look for errors surrounding that procedure. Make sure you filter the text based on the procedure name from Replication Monitor (It turns out that the command being executed was the stored procedure with a ;2 after it).

Then run EXEC sp_scriptpublicationcustomprocs 'PublicationName' on the publisher to get scripts to create the procedure. Create that procedure on the subscriber db including the group number that the process is attempting to run after the group name (e.g. [sp_MSins_dboTABLE];2). It feels hacky, but it will get replication functioning healthily again.

  • Well well well. When I recreated the publication and added the subscription, I started the snapshot agent. I never actually re-initialized since I didn't believe that it needed it (since I thought I was recreating). Could that be my problem? – swasheck Jul 23 '12 at 18:28
  • I have reinitialized the subscriptions and I still get the error that it cannot find the stored procedure. – swasheck Jul 23 '12 at 19:49
  • It sounds like the snapshot isn't being correctly deployed to the subscriber. Is the table being deleted from the subscriber and recreated? – mrdenny Jul 23 '12 at 22:10
  • I don't believe that they are. At least not automatically. It's been resolved down to two tables now. Reinitialization worked for all but these two – swasheck Jul 24 '12 at 3:19
  • Ultimately this resolved the majority of my issues. Thank you. – swasheck Jul 24 '12 at 19:59

Are you up to date on service packs?

Have you looked into this Microsoft support article?


  • That looks vaguely similar to my problem. However, the successful subscriptions also have composite keys. Having said that, I'm only at SP1 on my SQL 2008 instance. – swasheck Jul 20 '12 at 17:53
  • Applying the patch didnt work – swasheck Jul 20 '12 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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