September 22, 2004 at 4:26 pm
Hello,
How to alter a column of an replicated table?
Help, please!
September 22, 2004 at 8:29 pm
Hi what specifically do you want to do,
alter data in a column
or an attribute of the column eg varchar to int etc
Cheers
------------------------------
Life is far too important to be taken seriously
September 23, 2004 at 1:36 am
You can't directly modify an existing column's properties but you can add a new column or drop an existing column (see sp_repldropcolumn and sp_repladdcolumn in BOL). So you could creating a new column with the appropriate properties, add it to the article, copy the old column's data to it, synchronise, drop the old column, synchronise, create another new column with the old column's name, copy the 2'nd column's data, synchronise, then finally delete the 2nd column; but it's horribly messy!
Or drop the publication and recreate it - with attendant problems of modifying the new schema at the subscription.
HTH
David Saville
September 23, 2004 at 5:20 am
1. drop the subscription
2. remove the table(article) from the publication
3. Alter the column
4. Add the table(article) back into the publication
5. ReRun the snapshot
6. Create the subscription and start the merge agent; the new snapshot will be applied
Note: If you select the option of "subscriber already has schema and data" then you will need to alter the column at the subscriber table, as well, prior to creating the subscription (step 6).
September 24, 2004 at 9:42 am
Another option is to DTS the table out, then drop and readd the column through replication properties or sp_repldropcolumn, sp_repladdcolumn (see BOL), then update the records setting the replaced column. This has worked very well for us in the past.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply