change datatype in a replicated table 1

[AccordingToDale]

hey,

been a while since i've been here - new job doesn't let me spend time on forums. anyway...

what i need: alter a column by changing from nvarchar(500) to ntext. (simple, right?)

the issue: the table is part of a merge publication.

can i alter the column w/o dropping the publication? if so, how?

thanx.

d

 

[SQLSister]

from BOL

Microsoft® SQL Server™ 2000 supports common schema changes to an existing publication database. You can add columns to, and drop columns from, a published table without dropping and recreating the publications and subscriptions referencing that table.

you might want to search books online for replication and then look at the article called schema changes.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[AccordingToDale]

thnx for the reply. got me pointed in the right direction.

for anyone interested....


-- step 1
-- create a temp column in table to hold data
-- while altering schema
exec sp_repladdcolumn @source_object = 'Table_Name'
, @column = 'Temp_Column_Name'
, @typetext = 'ntext NULL'
, @publication_to_add = 'Publication_Name'


-- step 2
-- copy the data from original column into the temp column
update Table_Name set Temp_Column_Name = Original_Column_Name

-- step 3
-- drop the original original column
exec sp_repldropcolumn @source_object = 'Table_Name'
, @column = 'Original_Column_Name'


-- step 4
-- insert a new column with new criteria
exec sp_repladdcolumn @source_object = 'Table_Name'
, @column = 'Original_Column_Name'
, @typetext = 'ntext NULL'
, @publication_to_add = 'Publication_Name'

-- step 5
-- stick the data from the temp column into the new column
update Table_Name set Original_Column_Name = Temp_Column_Name

-- step 6
-- drop the temp column from the schema
exec sp_repldropcolumn @source_object = 'Table_Name'
, @column = 'Temp_Column_Name'