{Li C Zhao}

At my work, we have MSSQL with replication. The problem is we have 5 SQL subscribers. Our R&D department keep coming out with Store Procedures. In order to deploy these new store procedures to production, we need to run the script on every servers. This is not only time consuming, but bounded to human error.

In SQL, you can replicate store procedures with ’sp_addarticle’ and ’sp_addsubscription’. However, they don’t seem to work in our environment.

exec sp_addarticle  @publication =   '<publicationName>'
   ,  @article =  '<nameofthesp>'
   ,  @source_object =  '<nameofthesp>'
   ,  @type = 'proc exec'

exec sp_addsubscription  @publication =  '<publicationName>'
   ,  @article =  '<nameOfTheSP>'
   ,  @subscriber =  '<nameOfTheSubscriber>'
   ,  @sync_type = '(automatic|none)'

I came across this built in store procedure and it’s doing what we needed to do. Save the SQL script to some where accessible over the network and use ’sp_addscriptexec’ to run the script on all the subscribers. You will still need to run the script on the publisher.

WARNING: Be careful on the script you are running. Any data manipulation in the script will cause your replication to be out of sync. This should be only used for adding and altering store procedures.

exec sp_addscriptexec @publication = '<publicationName>'
   , @scriptfile = '\\<publicationServerNameOrIP>\d$\<SQLScript>.sql'

Tags: Database, Replication, SQL