Skip to content

sp-addscriptexec-transact-sql.md

Latest commit

 

History

History
83 lines (59 loc) · 4.47 KB

sp-addscriptexec-transact-sql.md

File metadata and controls

83 lines (59 loc) · 4.47 KB
title sp_addscriptexec (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql
ms.prod_service database-engine
ms.reviewer
ms.technology replication
ms.topic language-reference
f1_keywords
sp_addscriptexec
sp_addscriptexec_TSQL
helpviewer_keywords
sp_addscriptexec
ms.assetid 1627db41-6a80-45b6-b0b9-c0b7f9a1c886
author stevestein
ms.author sstein

sp_addscriptexec (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]

Posts a SQL script (.sql file) to all Subscribers of a publication. This stored procedure is executed at the Publisher on the publication database.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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

Arguments

[ @publication = ] 'publication' Is the name of the publication. publication is sysname, with no default.

[ @scriptfile = ] 'scriptfile' Is the full path to the SQL script file. scriptfile is nvarchar(4000), with no default.

[ @skiperror = ] 'skiperror' Indicates whether the Distribution Agent or Merge Agent should stop when an error is encountered during script processing. SkipError is bit, with a default of 0.

0 = the agent will stop.

1 = the agent continues the script and ignores the error.

[ @publisher = ] 'publisher' Specifies a non- [!INCLUDEmsCoName] [!INCLUDEssNoVersion] publisher. publisher is sysname, with a default of NULL.

Note

publisher should not be used when publishing from a [!INCLUDEssNoVersion] Publisher.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_addscriptexec is used in transactional replication and merge replication.

sp_addscriptexec is not used for snapshot replication.

To use sp_addscriptexec, the [!INCLUDEssNoVersion] service account must have read and write permissions on the snapshot location and read permissions on the location where any scripts are stored.

The sqlcmd utility is used to execute the script at the Subscriber, and the script is executed in the security context used by the Distribution Agent or Merge Agent when connecting to the subscription database. When the agent is run on a previous version of [!INCLUDEssNoVersion], the osql utility is used instead of sqlcmd.

sp_addscriptexec is useful in applying scripts to subscribers, and uses sqlcmd to apply the contents of the script to the Subscriber. However, because Subscriber configurations can vary, scripts tested prior to posting to the Publisher may still cause errors on a Subscriber. skiperror provides the ability to have the Distribution Agent or Merge Agent ignore errors and continue on. Use sqlcmd to test scripts prior to running sp_addscriptexec.

Note

Skipped errors will continue to be logged in the Agent history for reference.

Using sp_addscriptexec to post a script file for publications using FTP for snapshot delivery is only supported for [!INCLUDEmsCoName] [!INCLUDEssNoVersion] Subscribers.

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addscriptexec.

See Also

Execute Scripts During Synchronization (Replication Transact-SQL Programming)
Synchronize Data
System Stored Procedures (Transact-SQL)