Show TOC

sp_setrepdefmodeLocate this document in the navigation structure

Changes or displays the owner status of tables marked for replication, and enables or disables table-level SQL statement replication for a specific DML operation.

Syntax
sp_setrepdefmode <table_name >[, 'owner_on' | 'owner_off' | 
   ‘<SQLDML_option> [<SQLDML_option> [ ...]]’ [, ‘on’ | ‘off’ | ‘never’ ] | 
   ‘threshold’, ‘<value>’]
<SQLDML_option> ::= { U | D | I }
Parameters
table_name

The name of a table in the current database that has been marked for replication with sp_setreptable.

owner_on

Changes the owner status of the table so the table name and owner name are considered when the table is marked for replication. Enables replication of multiple tables of the same name with different owners.

owner_off

Changes the owner status of the table so that only the table name is considered when the table is marked for replication.

SQLDML_option
Any of these DML operations:

  • U – update

  • D – delete

  • I – insert select

When the table replication mode is set to any combination of UDI the RepAgent sends additional information to enable SQL statement replication for the specified DML operation.

on

Enables SQL replication of the DML operation specified.

off

Removes the table-level replication settings of SQL statements, whether or not the statements are specified in <option>; the database-level replication settings are followed.

never

Disables SQL statement replication, regardless of the database setting, and regardless of whether the UDI parameter is specified.

'threshold', 'value'

Specifies the minimum number of rows that a replicated SQL statement must impact before SQL statement replication is activated.

Examples
Example 1
Enables SQL statement replication for update, delete and insert select operations on table <t>:
1> sp_setrepdefmode t, 'UDI', 'on'
2> go
Example 2
Sets the threshold to 10. Adaptive Server will use SQL replication on table <t> if the DML statement affects at least 11 rows:
sp_setrepdefmode t, 'threshold', '10'
Example 3
Displays the SQL replication settings and the owner status of table <rs_ticket_history>:
1> sp_setrepdefmode rs_ticket_history,'udi'
2> go
The replication status for 'rs_ticket_history' is
currently  owner_off, 'udi'.
The replication threshold for table 'rs_ticket_history'
is '0'.
(return status = 0)
Example 4
Sets the threshold to the default value:
sp_setrepdbmode t, 'threshold', '0'
Usage

  • Use sp_setrepdefmode with RepAgent-enabled Adaptive Server databases.

  • If sp_setrepdefmode is executed with the table name only, it displays the SQL replication settings and owner status of the table.

  • Use sp_setrepdefmode to change the mode of the table. You cannot change the owner mode of tables with sp_setreptable.

  • If the owner_off option is supplied and the current mode of the table is “owner on,” sp_setrepdefmode checks that the table name is unique among all replicated tables in owner off mode. If the name is unique, sp_setrepdefmode changes the table mode to owner off. If the name is not unique, the procedure fails.

  • The default threshold is 50 rows, which means that Adaptive Server uses SQL statement replication if the DML statement affects at least 51 rows. To use the default threshold, set the threshold parameter to 0. The threshold parameter range is 0 to 10,000.

Permissions

sp_setrepdefmode requires “sa” or “dbo” permission or replication_role.

Related Information