Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Transactional Replication Error: Could not find stored procedure error and how to recover it by using sp_scriptpublicationcustomprocs


By:   |   Last Updated: 2014-07-24   |   Comments (7)   |   Related Tips: More > Replication

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:

ReplicationError Could not find stored procedure

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:

  1. 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.
  2. 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


Last Updated: 2014-07-24


get scripts

next tip button



About the author
MSSQLTips author Percy Reyes Percy Reyes is a SQL Server MVP and Sr. Database Administrator focused on SQL Server Internals with over 10+ years of extensive experience managing critical database servers.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 21, 2017 - 12:56:34 PM - Patrick Fitzgerald Back To Top

Does anyone know what will happen when you run sp_scriptpublicationcustomprocs if there are still subscriptions attached to the publisher that are working? Will this interfere with subscribers that aren't having issues.

Say I had one publication with three subscriptions. Something happens and the stored procs disappear from the publication database. We don't notice it until we attempt to rebuild one of the three subscriptions. 


Saturday, July 26, 2014 - 1:38:00 AM - Percy Reyes Back To Top

Hi Imran,

sp_scriptpublicationcustomprocs is a system store procedure.

The transactional INSERT, UPDATE, and DELETE stored procedures can be considered as user stored procedures and they can be customized and even the their names can be defined inside the settings of publications for each article.


Saturday, July 26, 2014 - 1:32:42 AM - Percy Reyes Back To Top

Hi jeff_yao,

It's very interesting what you did! I think everything depends on the environment where you are working, many solutions or work-arounds can work good according to one specific situation. Congratulations and thanks for sharing your good idea!

Yes, you are right about the con to use sp_scriptpublicationcustomprocs but it is also important don't have many articles per publication, it is another way to optimize replication.


Friday, July 25, 2014 - 1:37:03 AM - Imran Back To Top

*sp's are system defined or created by the user, if it is built-in can you name the store procedures.

 


Thursday, July 24, 2014 - 5:53:16 PM - jeff_yao Back To Top

Congrats for the first tip, Percy. Very well written.

I'd like to share my experience here. What I usually do in this case is I will script out publication table and create it in a test environment (no data needed) and then set up a publication with  this table as as the only article, and then create a similar type subscription, and then script out the triggers on the subscriber side and reapply back to the problematic subscriber database. (All the steps are automated) 

The advantage here is: since it is automated, I really do not have any manual work. 

The con to use sp_scriptpublicationcustomprocs is when you have multiple articles (like 100+) in a publication, it is a little pain to retrieve the needed triggers in a huge content  , meaning more manual work is needed.

Of course, my way has its own issue as well in that if the article has some specific filters, it will make the automation script more complex (fortunately my replicated tables are straight-forward with no filter at all so I can make the automation script really simple).

 


Thursday, July 24, 2014 - 4:30:29 PM - Percy Reyes Back To Top

Jeremy,

Thanks you so much!!!! I hope continue writing many more tips for SQL Community!

Regards,

Percy Reyes

 


Thursday, July 24, 2014 - 11:21:12 AM - Jeremy Kadlec Back To Top

Percy,

Congrats on your first tip and welcome to the team.  We look forward to more tips from you.

Thank you,
Jeremy Kadlec
MSSQLTips.com Co-Leader


Learn more about SQL Server tools