Today we had an error in Replication Monitor for one of or Distribution Agents in our Development Environment. The Agent was trying to replicate a DELETE command to the Subscriber and it had an error referencing a missing Replication Stored Procedure. In our Environment, INSERT, UPDATE, and DELETE Replication Stored procedures are created during Replication Setup and are used to perform the DML Changes for Articles(Tables) replicated from the Publisher Database to the Subscriber Database.
Issue: Removal of a Replication Stored Procedure caused the Distribution Agent to stop processing Commands.
Resolution: Executing sp_scriptpublicationcustomprocs 'InsertPublicationName' at the Publisher and Publication Database, I was able to very quickly generate the missing stored procedure and create it on that Subscriber.
Note:
1. Another option would be to get that missing stored procedure from the Subscriber Full Backup. But that may take a while.
2. IF there was any modifications to any of those Subscriber Replication Stored Procedures then obviously using sp_scriptpublicationcustomprocs at the Publisher would not be aware of any of those custom changes and instead you would need to use the Subscriber Full Backups to get the needed stored procedure.
3. If any errors relating to Replication Stored Procedure definitions, you can use sp_scriptpublicationcustomprocs at the Publisher Database and use SSMS Generate Scripts at the Subscriber to get the Replication Stored Procedures and then compare them.
Ex: Distributor and Subscriber definition needs to match for any Replication Error Handling:
exec sp_MSreplraiserror @errorid=20598, @param1=N'[dbo].[TableName]', @param2=@primarykey_text, @param3=13233
Resources:
sp_scriptpublicationcustomprocs (Transact-SQL)
|
|
|
|
|
|