Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

up vote 3 down vote accepted
+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.

share|improve this answer
    
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?

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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