sp_setreplicate

Enables or disables replication for an Adaptive Server table or stored procedure. It also displays the current replication status of a table or stored procedure.

Note: This system procedure is still supported, but its capabilities have been incorporated into the system procedures sp_setreptable and sp_setrepproc. sp_setreplicate sets the replication status of columns with text, unitext, or image datatype to do_not_replicate. To replicate text, unitext, or image columns, use the sp_setreptable system procedure instead of sp_setreplicate. To specify individual text, unitext, or image columns for replication, use sp_setrepcol after using sp_setreplicate or sp_setreptable.

Syntax

sp_setreplicate [object_name [, {'true' | 'false'}]]

Parameters

object_name – is the name of a table or stored procedure in the current database.
true – enables replication for the table or stored procedure.
false – disables replication for the table or stored procedure.

Examples

Example 1 – Displays the replication status for all of the tables and stored procedures in the current database.
```
sp_setreplicate
```
Example 2 – Displays the replication status for the publishers table.
```
sp_setreplicate publishers
```
Example 3 – Enables replication for the publishers table.
```
sp_setreplicate publishers, 'true'
```

Usage

Use sp_setrepproc to enable or disable replication of stored procedures when you are using function replication definitions. Use either sp_setrepproc or sp_setreplicate to enable or disable replication of stored procedures when you are using table replication definitions.
Use sp_setreplicate with no parameters to display a list of replicated tables or stored procedures in the database.
Use sp_setreplicate object_name without true or false to display the current replication status of the table or stored procedure.
If you use sp_reptostandby to mark a table for implicit replication to the standby database, text, unitext, or image columns set by sp_setreplicate or sp_setrepcol to do_not_replicate are treated as replicate_if_changed. Columns set as always_replicate or replicate_if_changed are treated as marked.
Because Adaptive Server Enterprise starts a transaction to execute replicated stored procedures, it is important to 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 Enterprise holds all the locks until the execution of the procedure is complete.