sp_setreptable
Enables or disables replication for an Adaptive Server table or displays the current replication status of a table.
sp_setreptable [<table_name >[, {'true' | 'false' | 'never'}
[, {owner_on | owner_off | null}] [, use_index]]]The name of the table marked for replication.
Explicitly marks the table for replication, regardless of whether the database is marked for replication or not.
Disables the replication status on a table that has previously been enabled for replication.
Disables replication on the table, regardless of the database replication setting.
Sets the mode of the table so that both the table name and owner name are considered when the table is marked for replication. Enables tables with the same name but different owner be replicated. This option is for Adaptive Server version 11.5 and later databases.
Sets the mode of the table so that only the table name is considered when the table is marked for replication. This is the default. It ensures that the name for each table marked for replication is unique. This option is for Adaptive Server version 11.5 and later databases.
Sets the default value of owner_off when you pass it to the owner parameter.
-
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 of the tables in the current database that you had marked for replication with sp_setreptable:
sp_setreptable
Displays the replication status for the <publishers> table:
sp_setreptable publishers
Enables replication for the <publishers> table:
sp_setreptable publishers, 'true'
Allows multiple tables named <publishers> each owned by different users to be replicated:
sp_setreptable publishers, 'true', owner_on
- Replicates table named <publishers> belonging to owner <dbo> and stored in database <pubs2>:
sp_setreptable 'pubs2.dbo.publishers', 'true', owner_on
- Removes the replication status of table <t1,> and drops the replication indexes if <t1> was initially marked for replication to use indexes:
sp_setreptable t1, 'false'
- To disable replication on table <tnever> in database <pdb>, use:
sp_reptostandby pdb, 'ALL' go sp_setreptable tnever, 'never' go
Use sp_setreptable with no parameters to display a list of replicated tables in the database.
Use sp_setreptable <table_name> without true or false to display the current replication status of the table.
When you include the owner_on option, multiple tables with the same table name but different owners may be replicated to replicate and warm standby databases. Make sure that the replication definition on the table also includes owner information or replication may fail.
If a table has been marked for replication with sp_setreptable, you can change the owner mode with the sp_setrepdefmode system procedure.
The replication index status order of precedence is: column, table, database. For example, in a database marked for replication using indexes, the table status overrides the index status.
When a large table containing one or more <text>, <unitext>, <image>, or <rawobject> columns is marked for replication, the internal process is performed in a single transaction and may take a long time. To speed up the process, use the use_index option to create a global nonclustered index for every <text>, <unitext>, <image>, or <rawobject> column.
With use_index, a shared-table lock is held while the global nonclustered index is created.
-
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.