I've looked everywhere on this one and not found one jot of info

All I wanna do is alter a Char(40) Column to a Char(80)

SQL 2k sp3 - Merge Replication

Can anyone tell me how to do this without having to Halt Production in a Live environment ?

Looks like either a Drop Subscription

or

Adding a Temp Column - Copy Data Over to it - Drop Original Column - recreate as Char(80) - Copy Data Back.

Both methods involve taking the system offline even though it's not for long.

This Column has no Constraints or anything

If this type of Schema Change is possible then this Column is the simplest.

Any help appreciated

thx

GW

PS. I hope someone posts on this one - I don't seem to have much response with Replication Q's

In general you can use sp_repladdcolumn sp_repldropcolumn to add or drop columns to a previously replicated table. You can also modify this via the UI on the article properties page. In some cases, other schema changes must include to reinitialize the publication.

As its a production database and you're looking for major change in column size then obviously you need to have outage to make sure it affected without any issues. (suggested)

In any case you have to define the outage if not do it in less traffic hour.

The other option of adding temp. column and export data and again re-define the alterations on previous columns is fine though it will not cause outage..

thx Satya

did'nt quite get what U meant by

Is this the same as adding - dropping - recreating the field as in my second option ?

Anyway I'll try
I'll post back if it fails

thx

GW

Yes, after a thorough look up I suggest to use sp_repladdcolumn sp_repldropcolumn SPs as its a schema change.

I used Enterprise Manager (Publication Properties) GUI to achieve same thing (sp_repladdcolumn etc) cos it's quicker & I'm Idle

It would of worked as well if it was'nt for the fact that the statement

Update my_Table SET NewCol = OldColumn

Left me with different Values in some of the records in the NewCol

dunno Why - we are using some kind of Binary Data in each Char Value.

The Extended Charachter set that displays with a select certainly looks different in about 5% of the records

O well - Onwards Forever Onwards

GW

Glad it worked, but always remember to use SPs and functions provided which will give you a chance to get to know the system functionality rather depending on EM, it helps.