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 }

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.

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
```

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.