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 runningsp_scriptpublicationcustomprocs(I executed the result in thedistributiondatabase) 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.
Best Answer
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
;2after 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.