Show TOC

sp_setrepprocLocate this document in the navigation structure

Enables or disables replication for a stored procedure or displays the current replication status of a stored procedure.

Syntax
sp_setrepproc [<proc_name> [,'false' | 'table' | 
      'function' [,'log_current' | 'log_sproc']]]
Parameters
proc_name

The name of a stored procedure in the current database.

false

Disables replication for the stored procedure.

table

Enables replication for a stored procedure associated with a table replication definition.

function

Enables replication for a stored procedure associated with a function replication definition.

log_current

Logs the execution of the stored procedure you are replicating in the current database, not the database where the replicated stored procedure resides.

log_sproc

Logs the execution of the stored procedure you are replicating in the database where the stored procedure resides, not in the current database. log_sproc is the default.

Examples
Example 1

Displays the replication status for all of the stored procedures in the current database. For each procedure, indicates whether it is enabled for replication at all, enabled using a function replication definition, or enabled using a table replication definition.

sp_setrepproc
Example 2

Displays the replication status for the upd_pubs stored procedure. Indicates whether the stored procedure is enabled for replication at all, enabled using a function replication definition, or enabled using a table replication definition.

sp_setrepproc upd_pubs
Example 3

Enables replication for the upd_pubs stored procedure for use with a function replication definition. The execution of upd_pubs is logged in the database where upd_pubs resides.

sp_setrepproc upd_pubs, 'function'
Example 4

Enables replication for the upd_pubs stored procedure for use with a table replication definition. The execution of upd_pubs is logged in the database where upd_pubs resides.

sp_setrepproc upd_pubs, 'table'
Example 5

Enables replication for the upd_pubs stored procedure for use with a function replication definition. The execution of upd_pubs is logged in the current database.

sp_setrepproc upd_pubs, 'function', 'log_current'
Example 6

Enables replication for the upd_publ stored procedure for use with a function replication definition. The execution of upd_pubs is logged in the database where upd_pubs resides.

sp_setrepproc upd_pubs, 'function', 'log_sproc'
Usage

  • Use sp_setrepproc with no parameters to display all replicated stored procedures in the database.

  • Use sp_setrepproc <proc_name> with no other parameters to display the current replication status of the stored procedure.

  • If you are using Adaptive Server version 11.5 or later, supported DDL commands and stored procedures executed inside a user stored procedure are copied to the standby database if the procedure is enabled for replication with sp_setrepproc.

    Supported DDL commands and stored procedures executed inside a user stored procedure are not copied to the standby database if the procedure is not enabled for replication with sp_setrepproc.

  • Because Adaptive Server starts a transaction to execute replicated stored procedures, keep these points in mind when you design procedures:

    • If a replicated stored procedure contains DDL commands (for example, create table), Adaptive Server Enterprise generates an error unless the database option “DDL-in-Tran” is enabled on the database.

    • If the replicated stored procedure contains transactions and rollback commands that roll back the transaction, the rollback command rolls back the execution of the entire procedure.

    • Because of the outer transaction, Adaptive Server holds all the locks until the execution of the procedure is complete.

Note

Errors occur when running the sp_setrepproc command with or without any parameters when the data capture mode is mscdc.

Related Information