sp_setrepproc

Description

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. This option is equivalent to executing sp_setreplicate on the procedure.

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.