Show TOC

set repmodeLocate this document in the navigation structure

Enables or disables, at the session level, the replication of update, delete, insert select, or select into as SQL statements.

set repmode {“on” <SQLDML_option> | “never” | “off” | ‘threshold’, ‘<value>’}
<SQLDML_option> ::= { U | D | I | S }
Any combination of these DML operations:
  • U – update

  • D – delete

  • I – insert select

  • S – select into

SQL replication settings defined using set repmode overrides those defined using sp_setrepdbmode or sp_setrepdefmode.


Enables SQL replication of DML operation specified.


Removes the session-level replication settings of SQL statements and returns to the database-level or table-level settings.


Specifies not to replicate SQL statements.

Example 1
To replicate only select into and delete as SQL statements for the duration of the session, use:
set repmode on 'DS' 
Example 2
To disable SQL statement replication for the duration of the session, regardless of the database or table-level settings, use:
set repmode never
Example 3
This example illustrates how session-level settings override object-level settings. This example replicates only update statements using SQL statement replication:
set repmode on 'U'
sp_setrepdefmode tabname, on, 'UDI'
Example 4

This example shows how to define the threshold at the session-level as 1000 rows:

set repmode ‘threshold’, ‘1000’
  • You can set the session-level options either at login by using a “login trigger”, or at the beginning of a batch. Your session settings overwrites the table or database settings.

  • Session-level settings are active only for the duration of the session. When you set the options inside a stored procedure or a trigger, the settings are reverted back to the table-level or database-level settings when the stored procedure or trigger execution terminates.