Session level

Use session option set repmode to set replication mode to SQL statement replication. You can specify session-level settings either during login by using a login trigger, or at the beginning of a batch. Session settings override both database-level and object-level settings.

Use set repmode on to enable SQL statement replication for the DML operation specified, for the duration of the session. Use set repmode off to remove all SQL statement replication settings at the session level.

For example, to replicate only select into and delete as SQL statements for the duration of the session, use:

set repmode on 'DS' 

The set options are active for the duration of the session. Options that you set inside a stored procedure are reverted to the default values when the stored procedure finishes.

Executing set repmode on enables SQL statement replication only if session-level option set replication on is set. This example does not enable SQL statement replication:

set replication off
go
set repmode on 'S'
go

This example enables SQL statement replication:

sp_reptostandby pdb, 'ALL'
go
set repmode on 'S'
go