August 26, 2009 at 2:25 pm
I have a table that is part of a very large transactional publication with over 100 subscribers. Because of this reinitializing replication is towards the bottom of my list of things to do, since it takes a few days to get everything back up and running. But I recently have encountered a need to change the size of a column. I need to change colA from varchar(50) to a varchar(150). I have read up on ways to do this and the one that seems like it would work the best is using sp_repladdcolumn to create a temporary colB then move everything from colA to colB, us sp_repldropcolumn to drop colA, create a new colA with the correct size, and then move everything from colB to colA and use sp_repldropcolumn to get rid of colB.
exec sp_repladdcolumn 'dbo.tableA','colB','varchar(150)','all'
go
update dbo.tableA
set colB = colA
go
exec sp_repldropcolumn 'dbo.tableA','colA'
go
exec sp_repladdcolumn 'dbo.tableA','colA','varchar(150)','all'
go
update dbo.tableA
set colA = colB
go
exec sp_repldropcolumn 'dbo.tableA','colB'
go
This is pretty much the code from an article on altering a column on a replicated table. http://www.sqlservercentral.com/articles/Replication/alteringacolumnonareplicatedtable/1666/
My problem is when I tried this in a test environment I got an error about adding a column with the same name as an existing column. I went back and did this all again and when I run sp_repladdcolumn I go and look at the subscriber and I see the column I added, but when I run the sp_repldropcolumn and go and look at the subscriber the column I have drop'd is still there, even though when I look at the publisher the table is gone. Is their something I am missing to actually get ride of the table?
August 26, 2009 at 3:14 pm
even more interesting is that the sp_repldropcolumn command seems to update the stored procedures for replication. Which leads me to think that after I have issued the sp_repldropcolumn command that I should be able to issue an alter table command to drop the column on all the subscribers since it has already been removed from the stored procs that replication uses to keep everything up to date.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply