set repmode

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

Syntax

set repmode {“on” <SQLDML_option> | “never” | “off” | ‘threshold’, ‘<value>’}

<SQLDML_option> ::= { U | D | I | S }

Parameters

SQLDML_option

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.

on

Enables SQL replication of DML operation specified.

off

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

never

Specifies not to replicate SQL statements.

Examples

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'
go
sp_setrepdefmode tabname, on, 'UDI'
go

Example 4

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

set repmode ‘threshold’, ‘1000’
go

Usage

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.