sp_register_custom_scripting (Transact-SQL)

  • Article

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.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_register_custom_scripting [ @type  = ] 'type'
    [ @value = ] 'value' 
    [ , [ @publication = ] 'publication' ]
    [ , [ @article = ] 'article' ]

Arguments

  • [ @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.

    Value Description

    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.

    Note

    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)

Remarks

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 Regenerating Custom Transactional Procedures to Reflect Schema Changes.

Permissions

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.

See Also

Reference

sp_unregister_custom_scripting (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance