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”}
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.
To replicate only select into and delete as SQL statements for the duration of the session, use:
set repmode on 'DS'
To disable SQL statement replication for the duration of the session, regardless of the database or table-level settings, use:
set repmode never
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
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.
