5

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:

https://gist.github.com/3152160

Edit:

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

Table already has a primary key defined on it.

Improve this question
2
  • Which database did you migrate, the publisher or the subscriber?
    – mrdenny
    Jul 23, 2012 at 16:47
  • Migrated the publisher
    – swasheck
    Jul 23, 2012 at 18:27

2 Answers 2

Reset to default
3
+200

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.

Improve this answer
8
  • 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, 2012 at 18:28
  • I have reinitialized the subscriptions and I still get the error that it cannot find the stored procedure.
    – swasheck
    Jul 23, 2012 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, 2012 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, 2012 at 3:19
  • Ultimately this resolved the majority of my issues. Thank you.
    – swasheck
    Jul 24, 2012 at 19:59
0

Are you up to date on service packs?

Have you looked into this Microsoft support article?

http://support.microsoft.com/kb/960571

Improve this answer
2
  • 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, 2012 at 17:53
  • Applying the patch didnt work
    – swasheck
    Jul 20, 2012 at 19:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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