sp_setrepcol

Sets or displays the replication status for text, unitext, or image columns.

Syntax

sp_setrepcol table_name [, {column_name | null}
	 [, {do_not_replicate | always_replicate | replicate_if_changed}]]
              [, use_index]

Parameters

table_name – The name of the replicated table. You must enable replication for the table using sp_setreptable before you execute sp_setrepcol.
column_name – The name of a text, unitext, or image column in the table. Specify null for the column name to set the replication status of all text, unitext, or image columns in the table.
do_not_replicate – Prevents Adaptive Server from logging replication information for the text, unitext, or image column. If the column has previously been marked to use an index for replication, setting do_not_replicate removes the index.
always_replicate – Causes Adaptive Server to log replication information for the text, unitext, or image column when any column in the row changes. This status adds overhead for replicating text, unitext, or image columns that do not change; however, it protects against data inconsistency from row migration or changes during non-atomic materialization.
replicate_if_changed – Causes Adaptive Server to log replication information for the text, unitext, or image column only when the text, unitext, or image column data changes. This status reduces overhead, but it may lead to data inconsistency from row migration or changes during non-atomic materialization.
use_index – Marks the column to use an index for replication on text, unitext, image, or rawobjects columns.

Examples

Example 1 – Displays the replication status for all text, unitext, or image columns in the au_pix table. au_pix must be marked for replication using sp_setreptable.
```
sp_setrepcol au_pix
```
Example 2 – Displays the replication status for the pic column in the au_pix table. pic must be a text, unitext, or image datatype column.
```
sp_setrepcol au_pix, pic
```
Example 3 – Specifies that the pic column (image datatype) in the au_pix table should have the replicate_if_changed status. (In this particular table in the pubs2 database, there are no other text, unitext, or image columns.)
```
sp_setrepcol au_pix, pic, replicate_if_changed
```
Example 4 – Specifies that all text, unitext, or image columns in the au_pix table should have the replicate_if_changed status.
```
sp_setrepcol au_pix, null, replicate_if_changed
```
Example 5 – Marks the column t (text datatype) as replicate_if_changed and uses an index for replication:
```
sp_setrepcol t1, t, replicate_if_changed, use_index
```

Usage

Use sp_setrepcol to specify how text, unitext, or image columns are replicated after you have enabled replication for the table with sp_setreptable.
You can also execute sp_setrepcol with a table name to display the replication status of all of the text, unitext, or image columns in the table, or with the table name and a text, unitext, or image column name to display the replication status of the specified column.
Using the replicate_if_changed option reduces the overhead of replicating text, unitext, or image columns. However, these restrictions and cautions apply:
- If you specify the replicate_if_changed status for a column, any replication definition that includes the column must also have the replicate_if_changed status.
- If you set the replication status of any column to replicate_if_changed, you cannot set autocorrection to “on” for any replication definition that includes the column.
- If you use non-atomic subscription materialization and you have set the replicate_if_changed replication status for any text, unitext, or image columns, Replication Server displays a message in the error log file. This message warns you that the data may be inconsistent if an application modified the primary table during subscription materialization.
- If your application allows rows to migrate into a subscription and you have set the replicate_if_changed replication status for any text, unitext, or imagecolumn, Replication Server displays a warning message in the error log when the row migrates into the subscription and the text or image data is missing.
  
  If a text, unitext, or image column with the replicate_if_changed status was not changed in an update operation at the primary table and the update causes the row to migrate into a subscription, the inserted row at the replicate table will be missing the text, unitext, or image data. Run the rs_subcmp program to reconcile the data in the replicate and primary tables.
  
  Row migration can occur when subscriptions have where clauses. Updating a column specified in the subscription where clause can cause a row to become valid for, or migrate into, the subscription.
  
  When this happens, Replication Server must execute an insert in the replicate database. An insert requires values for all of the columns, including text, unitext, or image columns that did not change in the primary database.
When tables are marked with sp_reptostandby, you cannot change the replication status of text, unitext, or image columns using sp_setrepcol; text, unitext, and image columns are always treated as replicate_if_changed.
If the warm standby application includes normal replication and you have marked tables with sp_reptostandby and sp_setreptable, text, unitext, or image data columns may be treated as always_replicate or replicate_if_changed.
- If text, unitext, or image columns marked by sp_setreptable are specified always_replicate (the default), all text, unitext, and image columns are treated as always_replicate.
- If text, unitext, or image columns are specified by sp_setrepcol as do_not_replicate or replicate_if_changed, all text, unitext, or image columns are treated as replicate_if_changed.
The order of the precedence on the index status is: column, table, database. If the table is marked to use indexes on text, unitext, image or rawobject columns, but you do not want to use indexes in one of the columns, the column status overrides the table status.
You cannot use drop index to manually drop indexes created for text, unitext, image, or rawobject replication. You can use only the supported replication stored procedures sp_reptostandby, sp_setreptable, and sp_setrepcol to change the replication index status.

Permissions

sp_setrepcol requires “sa” or “dbo” permission or replication_role.