Alter Tables structure of articles in Replication

Question

Post reply

Alter Tables structure of articles in Replication

Bhushan Kalla

Default port

Points: 1475
More actions
November 6, 2005 at 10:49 am

#88478

Hi,

I would like to run the stored procedure at Publisher database The stored procedure has "ALTER TABLE" commands (dropping FKs and recreate FKs). What is the best way to do this kind of operations. Thank you for your time and help.

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Chris Becker SSCrazy Points: 2585 More actions · Answer 1

In order to run ALTER TABLE script, you must drop your subscriptions. The script below also drops the article to allow for column adds/deletes/mods then re-creates pub article. This is not always necessary, perhaps only a dropsubscription is needed, this is entirely up to you...

--Drop Subscription & Article

exec sp_dropsubscription @publication = 'Pub1'

, @article = 'MyTable'

, @subscriber = 'MySubscrServer'

exec sp_droparticle @publication = 'fxDB6_Pub1'

, @article = 'MyTable'

-- Make DDL changes

ALTER TABLE MyTable

ALTER COLUMN...

-- Add article ***IMPORTANT: you should script your own article to capture pub properties

exec sp_addarticle @publication = N'Pub1', @article = N'MyTable', @source_owner = N'dbo'

, @source_object = N'MyTable', @destination_table = N'MyTable', @type = N'logbased', @creation_script = null

, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F3, @status = 16

, @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_MyTable', @del_cmd = N'CALL sp_MSdel_MyTable', @upd_cmd = N'MCALL sp_MSupd_MyTable', @filter = null

, @sync_object = null, @auto_identity_range = N'false'

-- Add Subscription(s)

exec sp_addsubscription @publication = 'Pub1'

, @article = 'MyTable'

, @subscriber = 'MySubscrServer'

, @destination_db = 'SubscrDBName'

, @sync_type = 'none' -- 'automatic' if you want Snapshot agent to create snapshot only for MyTable article

Regards,

ChrisB MCDBA OCP MyDatabaseAdmin.com

Chris Becker bcsdata.net