sp_repldropcolumn and sp_repladdcolumn: does it work?

Last Post 23 Jul 2004 11:01 AM by Ali baba_SQL. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Ali baba_SQL
New Member
New Member

--
23 Jul 2004 09:29 AM
Hi all,

I've heard of people using sp_repldropcolumn and sp_repladdcolumn to change table scema in replicated tables, but never had good experience with it myself.

I used:
sp_repldropcolumn @Source_object = 'MyTable', @column='MyColumn' --to drop specifuc column from replication

sp_repladdcolumn @Source_object = 'MyTable', @column='MyColumn', @typetext = 'DiffDataType' --to cahnge datatype


The datatype changed on the publisher, but did not replicate to subscribers after I started the snapshot agent.


Any suggetsions?


Ali baba_SQL
New Member
New Member

--
23 Jul 2004 11:01 AM
that's what I tried to use it for : transactional repl.
the distribution agent failed, snapshot said no articles available for replication. meanwhile, there was a new schema change.
Ali baba_SQL
New Member
New Member

--
23 Jul 2004 06:08 PM
well, you can drop and add a column, but this will delete all the data in that column.
Moreover, it will recreate the that column at the end of the existing columns (append it).


what if you need it to be in specific pplace (say 3rd column)
What if you need to change the datatype on the column that has data, and you don't want to loose the data. I know you can delete all subscribtions + publications, do your changes and recreate and repush all publications again, but is there a better way?

Thanks again
You are not authorized to post a reply.

Acceptable Use Policy