need help in transactional replication

[cyno]

Hi

What is the best way to alter a coulmn which is in transactional replication.

Cyno

 

[mrdenny]

You can't alter a column that has been published for replication. To alter the column you'll need to remove the replication, alter the column, then resetup the replication.

I just went through the list of stored procedures for replication, and I'll 95% sure that I'm correct in my above statement.

That said, you could do the following.
Add a new column to hold the data.
Update the table with the new column.
Remove the old column
Add a new column with the same name as the origional column with the new paramaters (not null, null, etc).
Update the table putting the data from the temp column into the new column.
remove the old column.

Depending on the size of your table, size of your database, and the speed of your replication, this could take several hours as I beleive that it will need to do a couple of snapshots during this procedure.

Take a look at the sp_repladdcolumn and sp_repldropcolumn in BOL.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)