Use the threshold parameter for the sp_setrepdbmode command to set thresholds at the database-level.
These examples show how to set the threshold at the database and table levels, and at the same time define operations at the different levels.
Example 1 This example shows how to set a different threshold at the database and table levels for the pubs2 database and table1 table:
Reset the threshold at the database-level to the default of 50 rows:
sp_setrepdbmode pubs2, ‘threshold’, ‘0’ go
Enable SQL statement replication of update, delete, insert, and select into operations for pubs2:
sp_setrepdbmode pubs2, ‘udis’, ‘on’ go
Trigger SQL statement replication for table1 in pubs2 only for the operations you defined in step 2 when these operations affect more than 1,000 rows:
sp_setrepdefmode table1, ‘threshold’, ‘1000’ go
Example 2 This example shows how to define the threshold at the database-level for pubs2, and at the same time define different operations for tables, such as table1 and table2 located in the pubs2 database:
Set the threshold at the database-level to trigger SQL statement replication when a data manipulation language (DML) statement affects more than 100 rows:
sp_setrepdbmode pubs2, ‘threshold’, ‘100’ go
Define a different set of operations for two specific tables, where you want the operations replicated using SQL statement replication. Update, delete, and insert operations are for table1 and delete operations are for table2:
sp_setrepdefmode table1, ‘udi’, ‘on’ go sp_setrepdefmode table2, ‘d’ ‘on’ go
In this example, when a delete operation executes against table2 or any DML on table1 executes, the threshold of 100 rows that you defined at the database-level triggers SQL statement replication when reached.
