Show TOC

setLocate this document in the navigation structure

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.

Note

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.