November 10, 2005 at 4:27 pm
I'm trying to drop a FK constraint in a transactional replication. I look around and doesn't sees can find a way to do it (preferrable in SQL script).
The one I try try to drop is like:
alter table email_profile
drop constraint IX_EMAIL_PROFILE
I really appreciate any help!!
November 10, 2005 at 5:24 pm
I am assuming you are doing this at the publisher.
You need to drop subscriptions, make change, add subscriptions. If you ddl script is changing the structure of the table, changing datatypes, adding/deleteing columns, you may want to drop published article, then re-add.
--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 - param values below may differ in your environment...
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 = 'automatic' -- change to 'none' & snapshot agent will not re-init
-- Start Snapshot agent - creates snapshot only for MyTable article
-- Distribution agents will ship to subscribers
Regards,
ChrisB MCDBA OCP MyDatabaseAdmin.com
Chris Becker bcsdata.net
November 10, 2005 at 6:18 pm
Yes, the method you mentioned is what I know. I'm just wondering there is any easier (one command) way to do. I did this way before, somehow is some odd situation, I had diffcult time to add an article back after been dropped. This is why I'm trying to see ther eare different method.
Anyway, thanks for your input.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply