Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

change datatype in a replicated table 1

Status
Not open for further replies.

AccordingToDale

Programmer
Jul 11, 2005
128
CA
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
 
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
 
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'

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top