April 12, 2006 at 11:45 am
We have a Transactional Replication setup between 2 servers. We need to change the datatype(s) of a column(s) for couple of tables. The problem is the schema changes like add column and drop column can be replicated using sp_addreplcolumn and sp_dropreplcolumn but changes like ALTER TABLE cannot be replicated. Is there any workaround? One solution floating on the table is to unpublish the articles (the tables involved) make the change on the publisher, make the same change on the subscriber and let them sync again. Does anyone know if there is there any other way?
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 12, 2006 at 12:12 pm
Instead of altering the column, you can just create a new column copy the data. drop the old column add it again (to maintain the column name, with the correct data type) copy it back publish the column.
Its only worthwhile if you have a lot of data...
April 12, 2006 at 12:46 pm
We do have lot of data. around 6-8 million rows in the tables that need to be altered.
I tried the new column-copy-drop-add-update-drop approach. It didnt work. Apparently the insert scripts for sp_MSins_tablename do not match on the publisher and subscriber. I am guessing the order of columns is also important. So we cannot take this approach.
The only other approach I need to test is to unpublish the articles, make the changes on both pub and sub and republish again.
If there is any other way please feel to throw in any opinions.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
April 13, 2006 at 5:34 am
Please have a look at this article for more info: http://www.replicationanswers.com/AddColumn.asp
Rgds,
Paul Ibison, SQL Server MVP
Paul Ibison
Paul.Ibison@replicationanswers.com
April 14, 2006 at 11:54 am
We tried that option. With 250 mi rows, it bombed.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply