Following are examples of how to add or change a column for the primary and destination tables.
To add a char column named zip (for zip code information) to the source and destination copies of the publishers table:
Use the Transact-SQL alter table command to add the column to the tables in Adaptive Server. See the Adaptive Server Enterprise Reference Manual for more information.
Use alter replication definition to add the same column to the publishers_rep:
alter replication definition publishers_rep add zip char(10)
If the column you added to the destination table has a different name than the source column, enter a command like this:
alter replication definition publishers_rep add zip as rep_zip char(10)
See “Adding columns in source and destination tables” and alter replication definition in Chapter 3, “Replication Server Commands” Replication Server Reference Manual for more information.
You can drop searchable columns from a replication definition only if they are not used in subscription or article where clause.
Use drop subscription to remove any subscriptions in which you want the where clause to exclude the searchable columns you are dropping. See “Using the drop subscription command”.
Use alter replication definition to drop the searchable column. For example:
alter replication definition publishers_rep drop searchable columns zip
(This example removes the zip searchable column from the publishers_rep replication definition.)
See alter replication definition in Chapter 3, “Replication Server Commands” in the Replication Server Reference Manual for more information.
Use create subscription to re-create subscriptions to the altered replication definition. See “Using the create subscription command”.
Replication Server depends on primary keys to find the correct rows at the replicate or standby table. To add a the column zip as a primary key to the replication definition, enter:
alter replication definition publishers_rep add primary key zip
To drop a primary key, enter:
alter replication definition publishers_rep drop primary key zip
To replace all primary key columns, first alter the corresponding replication definition to add the new primary keys, then drop the old primary key columns in the table.
WARNING! If all primary key columns are missing from the primary table, the DSI will shut down.
You cannot change the declared column datatype (the datatype in the primary table) if it is used in a subscription or article where clause.
You cannot change the rs_address datatype.
You can change the column datatype to text, unitext, image, rawobject, or rawobject in row only if it is not a primary key or searchable column.
To change the published (replicate) datatype, you must include the declared (primary) datatype of a column (whether it is being changed or not) and the [map to] clause.
Altering a column’s datatype and nullability affects the same column across all replication definitions for a table.However, changes between a rawobject or rawobject in row and its base datatype, affects only the current replication definition. See “Translating datatypes using HDS” for more information about HDS.
Use column nullability changes for text, unitext, image, and rawobject columns only.
To replicate data for the source column zip into a destination column named rep_zip2, enter:
alter replication definition publishers_rep alter columns with zip as rep_zip
Enter such a command when:
You alter the existing destination table to add column rep_zip.
You drop and re-create the destination table to contain the column rep_zip in place of the original column zip.
