Changing column length of a replicated table

Can I increase the length of a varchar column of table involved in transactional replication without dropping and recreating publication/subscription?

Any help/short-cuts/undocumented features greatly appreciated.

Regards

Opal

Are you using SQL Server 2005? If so, you can turn on @.replicate_ddl option (set it to 1) for a publication then use regular "ALTER TABLE ... ALTER COLUMN ..." DDL syntax to increase the length of the column, the DDL change will be automatically replicated to subscriber by replication. (@.replication_ddl options is automatically turned on if you use management studio to create the publication)

Hope that helps,

Zhiqiang Feng

|||

Thank you Zhiqiang for your reply, but sorry we're using 2000.

Opal

|||

Sorry there is no easy workaround in SQL Server 2000. The only alternative is that you can use sp_repldropcolumn then sp_repladdcolumn to drop and re-create the column, but that way you will lose all the data for that column.

Thanks,

Zhiqiang Feng