set

Description

Controls replication definition properties for a replicate connection.

Syntax

set {autocorrection | dynamic_sql} {on | off}
 for replication_definition
with replicate at data_server.database

Parameters

autocorrection: Prevents failures that might occur because of missing or duplicate rows in a replicated table. Default is off.

dynamic_sql: Controls whether the table will be considered for dynamic SQL application. Default is on.

on: Enables autocorrection or dynamic SQL for the specified replication definition.

off: Disables autocorrection or dynamic SQL for the specified replication definition.

replication_definition: The name of the replication definition whose autocorrection or dynamic SQL status you are changing.

data_server: The name of the data server with the replicate database for which you are changing the autocorrection or dynamic SQL status. If the replicate database is part of a warm standby application, data_server is the logical data server name.

database: The name of the replicate database where you are changing the autocorrection or dynamic SQL status. If the replicate database is part of a warm standby application, database is the logical database name.

Examples

Example 1

Enables autocorrection for the publishers_rep replication definition in the pubs2 database at the SYDNEY_DS data server:

set autocorrection on
 for publishers_rep
 with replicate at SYDNEY_DS.pubs2

Example 2

Disables dynamic SQL for the publishers_rep replication definition in the pubs2 database at the SYDNEY_DS data server:

set dynamic_sql off
for publishers_rep
with replicate at SYDNEY_DS.pubs2

Usage

Use set dynamic_sql off to disable dynamic SQL commands for the specified replication definition and replicate connection.
Use set autocorrection to prevent duplicate key errors that might occur during non-atomic materialization.
Autocorrection should be enabled only for replication definitions whose subscriptions use non-atomic materialization (create subscription specified without holdlock). After materialization is complete and the subscription is VALID, disable autocorrection to improve performance.
Autocorrection is off, by default, for a replication definition.

How autocorrection works

set autocorrection determines how Replication Server processes inserts and updates to replicated tables. When autocorrection is on, Replication Server converts each update or insert operation into a delete followed by an insert.

For example, if a row inserted into the primary version of a table already exists in a replicated copy and autocorrection is off, the operation results in an error. When autocorrection is on, Replication Server converts the insert to a delete followed by an insert so that the insert cannot fail because of an existing row.

If the primary key has changed in a row that is to be replicated, Replication Server deletes two rows in the replicated table before it inserts the row. It deletes the row in which the primary key matches the before image and the row in which the primary key matches the after image.
When autocorrection is on, an insert or update at a primary database may cause delete and insert triggers to fire at the replicate database. The delete trigger fires only if the row inserted or updated at the primary database was already present at the replicate database.
Replication Server creates entries for replication definitions with autocorrection enabled in the rs_repobjs system table.

Autocorrection and replicated stored procedures

Replication Server does not perform autocorrection for rows updated at replicate databases as the result of using replicated stored procedures that modify primary data. See the Replication Server Administration Guide Volume 1 for more information about replicating stored procedures.

If you use replicated stored procedures to modify primary data, be sure to write stored procedures at the replicate Replication Server to correct for the failed updates and inserts that can occur during non-atomic materialization. Stored procedures at the replicate Replication Server should simulate autocorrection, treating update and insert operations as combined delete-insert operations. Alternatively, stored procedures can correct failed updates and inserts after they are detected.

Autocorrection and replicate minimal columns

If a replication definition uses replicate minimal columns, you cannot set autocorrection on. If you set autocorrection on before specifying minimal columns (for example, using alter replication definition), autocorrection is not performed. Replication Server logs informational messages for any update operations.

Autocorrection and text, unitext, or image datatypes

If a replication definition has a text, unitext, or image column in the replicate_if_changed column list, an attempt to enable autocorrection for the replication definition causes an error. Autocorrection requires that all text, unitext, and image columns appear in the always_replicate list for the replication definition.

Autocorrection and bulk copy-in

In normal replication, bulk operation is disabled if autocorrection is on. However, in subscription materialization, bulk copy-in is applied even when autocorrection is enabled except for nonatomic subscriptions recovering from failure.

Permissions

set requires “create object” permission.

set