Alter column in a table that is being published for replication.

Ever get this wonderful error?
"Cannot alter the table 'XXXX' because it is being published for
replication."
Many of the posts I have seen regarding this subject suggest that you
"trick" the server into thinking that the table is not being replicated
by messing with the system tables. Then change the column, then update
the syswhatever table again.
The following solution does not touch the system tables. Probably best
implemented as a stored procedure. Tested on MSSQL 2000 to MSSQL 2000
transactional replication.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/*ALTER COLUMN WITH TRANSACTIONAL REPLICATION*/
declare @.tablename varchar(55)
declare @.columnname varchar(55)
declare @.datatype varchar(15)
declare @.strSQL varchar(5000)
set @.tablename = 'YOUR TABLENAME HERE'
set @.columnname = 'YOUR COLUMN NAME HERE'
set @.datatype = 'NEW DATA TYPE'
/************************************************** **********************
in order to change a column without dropping and recreating
subscriptions
we have to:
1) create a dummy column
2) copy data from original column to dummy column
3) drop the original column
4) create the original column with the new parameters
5) copy data from dummy column into new original column
6) drop the dummy column
************************************************** ***********************/
--create dummy column
set @.strSQL = 'sp_repladdcolumn @.source_object = ''' + @.tablename +
''', @.column = ''temp_column'', @.typetext = ''' + @.datatype + ''''
exec(@.strSQL)
--put the data in the dummy column
set @.strSQL = 'update ' + @.tablename + ' set temp_column = ' +
@.columnname
exec(@.strSQL)
--drop the original column
set @.strSQL = 'sp_repldropcolumn @.source_object = ''' + @.tablename +
''', @.column = ''' + @.columnname + ''''
exec(@.strSQL)
--re-create the original column with new parameters
set @.strSQL = 'sp_repladdcolumn @.source_object = ''' + @.tablename +
''', @.column = ''' + @.columnname + ''', @.typetext = ''' + @.datatype +
''''
exec(@.strSQL)
--copy data from dummy column to new original
set @.strSQL = 'update ' + @.tablename + ' set ' + @.columnname + ' =
temp_column'
exec(@.strSQL)
--drop the dummy column
set @.strSQL = 'sp_repldropcolumn @.source_object = ''' + @.tablename +
''', @.column = ''temp_column'''
exec(@.strSQL)
/*END ALTER COLUMN WITH TRANSACTIONAL REPLICATION*/
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enjoy!
Ryan,
this looks as though it could be useful, and thanks for sharing your
personal code.
From within a sp though, I'd change the following type of code
set @.strSQL = 'sp_repladdcolumn @.source_object = ''' + @.tablename +
''', @.column = ''temp_column'', @.typetext = ''' + @.datatype + ''''
exec(@.strSQL)
to
exec sp_repladdcolumn @.source_object = @.tablename, @.column = 'temp_column',
@.typetext = @.datatype
(I think the update is the only bit that needs to be dynamic sql).
Rgds,
Paul Ibison