How to change column length on published DB - Merge Replication

Guys,
I have Merge Replication on SQL SERVER 2000.
Nilay,
Directly this can't be done. Indirectly it can, but not nicely! You could add a new column with the new datatype (sp_repladdcolumn), do an update on the table to populate the column, then drop the column (sp_repldropcolumn). Do this again to create the co
lumn having the same original name. Hopefully this functionality will be directly available in SQL Server 2005.
HTH,
Paul Ibison
|||The sp_repladdcolumn proc has a @.schema_change_script parameter where you can package a update statement to simplifiy the operation of this.
ie
update table TableYouAreModifiying set newColumn=oldColumn
Here is a code sample of how to do this using SQL-DMO
Option explicit
dim objServer, objReplication, objReplicationDatabases,_ objReplicationDatabase, objReplicationTables,_ objReplicationTable
set objServer=CreateObject("SQLDMO.SQLServer")
objServer.Connect "hilary2kp" , "sa","se1cure#"
set objReplication=objServer.Replication
set objReplicationDatabases=objReplication._
ReplicationDatabases
set objReplicationDatabase=objReplicationDatabases("pu bs")
set objReplicationTables=objReplicationDatabase._
ReplicationTables
for each objReplicationTable in objReplicationTables
if objReplicationTable.Name ="authors" then
objReplicationTable.ReplicationAddColumn _ "temp","char(100)","pubs",_
"c:\temp\updatetemp.sql"
objReplicationTable.ReplicationDropColumn _
"au_lname"
objReplicationTable.ReplicationAddColumn _
"au_lname","char(100)","pubs",_
"c:\temp\updatetemp2.sql"
objReplicationTable.ReplicationDropColumn "temp"
end if
next
set objReplicationTable=Nothing
set objReplicationTables=Nothing
set objReplicationDatabase=Nothing
set objReplicationDatabases=Nothing
set objReplication=Nothing
set objServer=Nothing
Here are my update scripts
updatetetmp.sql
update authors set temp=au_lname
updatetemp2.sql
update authors set au_lname=temp
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks Guys,
This helps.