Replication allows user-defined custom stored procedures to replace one or more of the default procedures used in transactional replication. When a schema change is made to a replicated table, these stored procedures are re-created. sp_register_custom_scripting registers a stored procedure or Transact-SQL script file that is executed when a schema change occurs to script out the definition for a new user-defined custom stored procedure. This new user-defined custom stored procedure should reflect the new schema for the table. sp_register_custom_scripting is executed at the Publisher on the publication database, and the registered script file or stored procedure is executed at the Subscriber when a schema change occurs.
sp_register_custom_scripting [ @type = ] 'type' [ @value = ] 'value' [ , [ @publication = ] 'publication' ] [ , [ @article = ] 'article' ]
[ @type = ] 'type'
Is the type of custom stored procedure or script being registered. type is varchar(16), with no default, and can be one of the following values.
|insert||Registered custom stored procedure is executed when an INSERT statement is replicated.|
|update||Registered custom stored procedure is executed when an UPDATE statement is replicated.|
|delete||Registered custom stored procedure is executed when a DELETE statement is replicated.|
|custom_script||Script is executed at the end of the data definition language (DDL) trigger.|
[ @value = ] 'value'
Name of a stored procedure or name and fully-qualified path to the Transact-SQL script file that is being registered. value is nvarchar(1024), with no default.
Specifying NULL for valueparameter will unregister a previously registered script, which is the same as running sp_unregister_custom_scripting.
When the value of type is custom_script, the name and full path of a Transact-SQL script file is expected. Otherwise, value must be the name of a registered stored procedure.
[ @publication = ] 'publication'
Name of the publication for which the custom stored procedure or script is being registered. publication is sysname, with a default of NULL.
[ @article = ] 'article'
Name of the article for which the custom stored procedure or script is being registered. article is sysname, with a default of NULL.
Return Code Values
0 (success) or 1 (failure)
sp_register_custom_scripting is used in snapshot and transactional replication.
This stored procedure should be executed prior to making a schema change to a replicated table. For more information about using this stored procedure, see Regenerate Custom Transactional Procedures to Reflect Schema Changes.
Only members of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role can execute sp_register_custom_scripting.