sp_setrepcol
Sets or displays the replication status for <text>, <unitext>, or <image> columns.
sp_setrepcol <table_name> [, {<column_name> | null}
[, {do_not_replicate | always_replicate | replicate_if_changed}]]
[, use_index]The name of the replicated table. You must enable replication for the table using sp_setreptable before you execute sp_setrepcol.
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.
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.
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.
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.
-
Marks the database to use an index for replication on text, unitext, image, or rawobjects columns, and internal indexes are created on those tables not explicitly marked for replication.
The use_index option only takes effect in tables with LOB columns that you created with versions of Adaptive Server earlier than 15.7 SP100. With Adaptive Server 15.7 SP100, use_index is deprecated because the information RepAgent needs to replicate LOB columns is already available in the form of backlinking pointers and therefore RepAgent ignores use_index when you upgrade the database to version 15.7 SP100 and later.
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
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
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
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
-
Disables replication of compressed LOB columns:
sp_setrepcol <table_name>, <lob_column_name>, ‘do_not_replicate’
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 <image>column, 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.
-
Replication of the Transact-SQL writetext command requires access to the data row pointing to the text page where the database stores the LOB data. To allow access to this data row, Adaptive Server uses either a backlink pointer in the first text page or indexes created for replication. The process of creating indexes at the column, table or database level requires an intensive operation to provide the information to support replication.
With an Adaptive Server version 15.7 SP100 and later database that you did not upgrade from an earlier version, sp_reptostandby takes effect immediately because by default, Adaptive Server creates and maintains LOB backlinking pointers to the database. Therefore, setting up replication for a table does not require the creation of indexes. Adaptive Server ignores the use_index parameter if the information needed to replicate LOB columns is already available in the form of backlinkingpointers.
However, if you have upgraded from, or are using a database that you created with a version of Adaptive Server earlier than 15.7 SP100, setting up replication may take a longer time due to the creation of indexes. To reduce processing time, run dbcc shrinkdb_setup at the relevant level—column, table, or database, to create backlinking pointers and to ensure the backlinking status is up to date.
dbcc shrinkdb_setup marks as suspect, replication indexes of columns, tables, or databases that you previously marked with use_index. You can use dbcc reindex to drop indexes for these objects because these indexes are not needed after the execution of dbcc shrinkdb_setup.
-
With Adaptive Server 15.7 SP100, use_index is deprecated. With versions earlier than Adaptive Server 15.7 SP100, if you use use_index, a shared-table lock is held while the nonclustered index is created.