Altering tables in replicated databases

• Gordon Pollokoff

  SSCommitted

  Points: 1930

  More actions

  February 23, 2005 at 10:03 am

  #92458

  Please bear with me, as replication is a new field for me......

  I have 150 Replicated Databases (Merge Replication) for which I need to modify a table.  The modification is to change a column from varchar(2) to varchar(4).  Reading about schema changes in BOL clues me in to sp_repladdcolumn and sp_repldropcolumn.  However, I do not wish to drop and add; rather I want to modify an existing column.

  How does one script this and preserve the existing data?

  Gordon Pollokoff

  "Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 23, 2005 at 2:17 pm

  #543782

  the idea is something like:

  -Create a new column with the target datatype : sp_repladdcolumn

  -Run : Update TableName set Targetdatatye column = source data

  -Drop old column : sp_repldropcolumn

  -Synchronize

  -Create a new column with the same name as the old : sp_repladdcolumn

  -Run : Update TableName set Final column =  Tmp Column

  - Drop Tmp column : sp_repldropcolumn

  -Synchronize

  YUCK

   

  There is a shortcut.

  You could create a temp Table with target datatype 

  -Update the temptable column with the source values

  -drop the old column : sp_repldropcolumn

  -Synch

  -add the new column : sp_repladdcolumn

  -Update new column with the data from the temp table and drop temptable

  Not very pretty either but that's how it goes so far

   

   

  HTH

  
  * Noel