Problem
One of the most important aspects in a SQL Server database server environment is security. This includes ensuring no object is dropped or modified as an error or intentionally without proper authorization. Many developers ignore, for example, the “sp_MS%” stored procedures and believe they can be deleted during their deployments. Unfortunately, when one of the transactional INSERT, UPDATE, and DELETE stored procedures at the subscriber is dropped, then the error “Could not find stored procedure sp_MS%” will be raised”. For example if the DELETE stored procedure is dropped, then when a DELETE operation is replicated to the subscribers the error will tell us that this procedure is missing and the DELETE operation cannot be replicated. In other words, the DELETE command cannot be moved from the distributor to the subscribers and replication for that article will be stopped. Check out the solution for tip to learn how to fix this issue.
Solution
INSERT, UPDATE, and DELETE stored procedures are objects, created during the re-initialization process by SQL Server replication, to perform the data changes for an article replicated from the publisher to the subscribers. For example if you have a table named My_Table (with columns COL1, COL2, COL3) published as part of a transactional publication article, then SQL Server will create INSERT, UPDATE, and DELETE stored procedures in the database subscriber. For example, the DELETE procedure may be coded like this:
USE [my_subscriber_db]
GO
create procedure [dbo].[sp_MSdel_dbomy_table]
@pkc1 int
as
begin
delete [dbo].[my_table]
where [COL1] = @pkc1
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
Now if one of these stored procedures are dropped then we will have a replication error as I explained in the problem description. Here is a screen shot:
sp_scriptpublicationcustomprocs SQL Server System Stored Procedure
In order to fix this error we will need to recover the missing procedure and deploy it to the subscribers. What happens if you do not have any backups of this stored procedure? How can you recreate the stored procedure? The answer is using the sp_scriptpublicationcustomprocs system stored procedure which has been available since SQL Server 2000 Service Pack 1. The only parameter for the sp_scriptpublicationcustomprocs is the publication name and this stored procedure must be executed on the published database. Here is some sample code:
--We will assume that my_table table is in my_published_db and it is part of my_publication. USE my_published_db GO EXEC sp_scriptpublicationcustomprocs @publication='my_publication'
The output from this stored procedure will be the creation code of INSERT, UPDATE, and DELETE stored procedures for each article that belongs to the ‘my_publication’ publication. This DELETE procedure must be copied and executed on the subscriber database. In a few seconds the Distribution Agent will use the new procedure and be able to replicate the data changes to the subscriber.
How does the sp_scriptpublicationcustomprocs work? It reads the replication metadata from your publication and executes inside another replication stored procedures such as sp_scriptinsproc, sp_scriptdelproc (or sp_scriptxdelproc) and sp_scriptupdproc (or sp_scriptxupdproc) for each article in order to generate the INSERT, UPDATE and DELETE procedures.
Alternatives to Rebuild the SQL Server Replication Stored Procedures
What happens if numerous or hundreds of stored procedures are dropped? Even if you have a backup of all of these stored procedures, the deployment can take a great deal of time because of the quantity of stored procedures and deploying them one by one. Are there any other options?
We have two options to rebuild a large number of SQL Server Replication Stored Procedures:
- You can reinitialize the subscriptions to recreate new INSERT, UPDATE, and DELETE stored procedures on the subscription side. To be honest, I do not recommend this option because it may be a resource intensive task and not productive especially for big databases or tables. Reinitializing can also this block other users during the synchronization process.
- You can use sp_scriptpublicationcustomprocs system stored procedure to recreate the creation code of the INSERT, UPDATE, and DELETE stored procedures for one particular publication. This option is a bit more feasible and what I generally recommend.
*** Note ***
Many companies add customized business logic inside of the INSERT, UPDATE, and DELETE replication stored procedures, so the sp_scriptpublicationcustomprocs will not be able to recreate this customization logic. The sp_scriptpublicationcustomprocs only scripts the default creation code based on the metadata. If one of the customized transactional INSERT, UPDATE, and DELETE stored procedures is dropped you will need to recover it from a database backup, creations scripts or your source control system. As a final recommendation you should always save all these system replication stored procedures in a deployment folder with your ticket or in your source control system.
Next Steps
- Read these additional resources related to the “Could not find stored procedure sp_MS%” error:
- Check out all of the SQL Server Replication tips on MSSQLTips.com.
