how to drop a constraint in transactional replication

  • 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!!

     

  • 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

  • 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