Executing scripts with sp_addscriptexec

Looking for ways to do things more quickly and efficiently, I thought I would talk a little bit about sp_addscriptexec and how it can be leveraged to expedite the process of executing SQL scripts in a replication topology.  By creating and saving a SQL script on a publisher, it can then be distributed to and executed on UNC/FTP deployed subscribers using sp_addscriptexec.

The syntax for sp_addscriptexec per BOL

sp_addscriptexec [ @publication = ] publication
[ , [ @scriptfile = ] 'scriptfile' ]
[ , [ @skiperror = ] 'skiperror' ]
[ , [ @publisher = ] 'publisher' ]

The problem with numbers

If you’re dealing with a large number of subscribers, database administration can be tricky.  Tasks such as adding logins and users, granting permissions, maintaining indexes, and managing constraints must be done individually at each node and deploying all of the scripts can be very time consuming.  Rather than visit each node, sp_addscriptexec should be used to post the ad-hoc script to all subscribers in the topology, saving valuable time.  Put simply – if you’re visiting each node to execute a script, you’re doing it wrong.

The benefit with using sp_addscriptexec is that the publisher can act as a centralized script repository.  Scripts can be saved to the publisher and executed on demand for subscribers.  This process is quicker and more efficient than copying, saving, and executing scripts directly at each subscriber.  Not only does this save time, but space as well.

Executing scripts

Applying scripts to subscribers from the publisher can be done by:

  • Create and test the script
  • Save the script to the publisher
  • Execute sp_addscriptexec at the publisher to apply script to subscribers

From here, the script will be executed at each subscriber on the next synchronization.  Taking this one step further — to limit a script to run on only certain subscribers, a check for HOST_NAME can be performed in the script.

-- Limit script to Subscriber1, Subscriber2, and Subscriber3
IF HOST_NAME() IN ('Subscriber1', 'Subscriber2', 'Subscriber3')
	-- script goes here

Something to consider

A caveat with sp_addscriptexec is that by default, if the script being executed encounters an error at the subscriber, the replication agent will fail and retry on subsequent syncs in an endless loop, be aware of this.  This behavior can be overridden by passing a value of 1 for @skiperror which instructs the agent to skip errors.  At the very least, SQL scripts should be tested thoroughly at the publisher before calling sp_addscriptexec to apply them to subscribers.

Discuss
9 Comments
  1. [...] Executing scripts with sp_addscriptexec – I do a lot of Replication administration and so I was shocked that I did not know about this! An excellent tip brought to our attention courtesy of Brandon Williams (Blog|Twitter). [...]

  2. Adithya Kalyan says:

    Is this any different from Post Snapshot or Presnapshot scripts in Transactional Replications?

  3. Farid Kettani says:

    I tried to run this sp and I keep getting this message even when I give full control to this folder to everyone :
    Msg 21331, Level 16, State 1, Procedure sp_MSreplcopyscriptfile, Line 49
    Cannot copy user script file to the snapshot folder at the Distributor (copy “C:trgManagement.sql” “E:\MSSQL.1\MSSQL\ReplData\20131101163055520″). Ensure that there is enough disk space available, and that the account under which the Snapshot Agent runs has permissions to write to the snapshot folder and its subdirectories..

    • Hi Farid,

      It is because you specified an incorrect path to your script file. It should be:

      EXEC sys.sp_addscriptexec
      @publication = ‘PublicationName’,
      @scriptfile = ‘C:\trgManagement.sql’,
      @skiperror = 1

      Notice you are missing the backslash ‘\’ in your path to the script file.

  4. Alejandro Palacios says:

    Hi,

    We are looking to executing a script after the snapshot is applied, not only after initialization (post snapshot script), but whenever we have to re-create the snapshot and apply it (this is not reinitialization). For example, we may drop constraints for a table, and re-create them on the publisher, this forces to invalidate the snapshot, when done, we re-create the snapshot and it is immediately applied to the subscriber, I want to run a script after that is done, is this possible ?

    We are pulling our hairs here, I appreciate your help

  5. Hi Alejandro. Yes, immediately after you invalidate the snapshot, regenerate a new snapshot, and it is applied to the subscriber, execute sp_addscriptexec to post your script to the subscriber.

    However, you may not need to invalidate the snapshot when you drop and recreate your constraints. Which type of replication are you using and how are you dropping and recreating your constraints?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>