Skip to content

Latest commit

 

History

History
40 lines (30 loc) · 2.41 KB

execute-scripts-during-synchronization-replication-transact-sql-programming.md

File metadata and controls

40 lines (30 loc) · 2.41 KB
Raw
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords dev_langs monikerRange
Execute scripts during synchronization (Replication SP)
Learn how to use Replication stored procedures to execute on-demand scripts during the synchronization process of a Transactional or Merge Publication.
MashaMSFT
mathoma
03/14/2017
sql
replication
how-to
updatefrequency5
synchronization [SQL Server replication], scripts
scripts [SQL Server replication], synchronization and
sp_addscriptexec
TSQL
=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

Execute Scripts During Synchronization (Replication Transact-SQL Programming)

[!INCLUDE SQL Server] Replication supports on demand script execution for Subscribers to transactional and merge publications. This functionality copies the script to the replication working directory and then uses sqlcmd to apply the script at the Subscriber. By default, if there is a failure when applying the script for a subscription to a transactional publication, the Distribution Agent will stop. You can specify a [!INCLUDEtsql] script to execute programmatically using replication stored procedures.

To specify a script to run for all Subscribers to a snapshot, transactional or merge publication

  1. Compose and test the [!INCLUDEtsql] script that will be executed on demand.

  2. Save the script file to a location where it can be accessed by the Snapshot Agent for the publication.

  3. At the Publisher on the publication database, execute sp_addscriptexec (Transact-SQL). Specify @publication, the name of the script file with full UNC path created in step 2 for @scriptfile, and one of the following values for @skiperror:

    • 0 - the agent will stop executing the script if an error is encountered.

    • 1 - the agent will log errors and continue executing the script when errors are encountered.

  4. The specified script will be executed at each Subscriber when the agent next runs to synchronize the subscription.

See Also

Synchronize Data