Enable SQL Statement Replication at the Session Level

Use set repmode to configure SQL statement replication for the DML operation specified, for the duration of the session. Session settings override both database-level and object-level settings.

You can specify session-level settings either during login by using a login trigger, or at the beginning of a batch.

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

set repmode on 'DS'

Use set repmode off to remove all SQL statement replication settings at the session level.

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.

Note: When you set options inside a login trigger, the option settings are maintained after the trigger has finished executing.

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

The threshold parameter defines the minimum number of rows that a DML statement must affect, to activate SQL statement replication. The default threshold is 50 rows, which means that Adaptive Server automatically uses SQL statement replication if the DML statement affects at least 51 rows.

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

set repmode ‘threshold’, ‘1000’
go

See Replication Server Reference Manual > Adaptive Server Commands and System Procedures > set repmode.