Replication: Changing the size of a column in an article/table?

[EIL]

I need to change the size of a column in one table from a char(15) to a char(25) in MS SQL 2000.

Sounds easy enough doesn't it?

However, the table is marked for replication and doesn't seem to want to have it's size changed.

Can anyone help on this issue? I have been looking everywhere for over an hour now and I can't find a solution.

Thank you in advance for any help you can provide.

Cheers,

Oli.

 

[Transcend]

Hi there

I think to make changes to a database that is being replicated, you have to stop the replication, make the change, re-run the initial snap shot and begin replication again

Hope this helps

Transcend

 

[EIL]

Running an initial snap shot is not an option due to line limitations and the large amount of data involved.

Is there a stored procedure or something similar?

Oli.

 

[Transcend]

Hi there

i think you're in trouble if you can never run the initial snap shot again!! You may have to do it outside of office hours, but what if replication fails or you need to change one of the servers one day ...
You definately cannot make a change until you stop replication, and you'll need to run the snap shot again so that the column sizes match up - otherwise you'll get errors with your replication.

Transcend

 

[EIL]

Obviously if we needed to run an initial snapshot in one of those circumstances then we could.

What if I stop replication, make the change to the field size on the publication db, then do the same on the subscriber db, changed the field sizes in the replication stored procs and then started replication back up, would that work?

Oli

 

[Transcend]

Hi Oli,

I'm not exactly sure but it sounds reasonable ... all I know is that to make the change you'll have to stop replication first.

Transcend

 

[dmooreora]

I just build a transaction replication at a customer site because I felt that it would give them the kind of fail-over that they required. Unfortunately, I never realized how difficult it would be to simply drop tables, change columns, or do other DDL.

If you want to drop a column, then recreate it (with a different domain, datatype, etc), you have to right click on publisher, go to properties, go to column filters (or filters).
This utility allows you to drop and recreate columns. It's tricky.

You may want to create a new snap shot, but I don't thinks this is neccesary for a column change.

Dmooreora