Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: Replication - Alter Column Data Type (How)

    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
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    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..
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    thx Satya

    did'nt quite get what U meant by

    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
    Is this the same as adding - dropping - recreating the field as in my second option ?

    Anyway I'll try
    Adding a Temp Column - Copy Data Over to it - Drop Original Column - recreate as Char(80) - Copy Data Back.
    I'll post back if it fails

    thx

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Yes, after a thorough look up I suggest to use sp_repladdcolumn sp_repldropcolumn SPs as its a schema change.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    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
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  6. #6
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    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.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •