Post reply

Alter Tables structure of articles in Replication

  • 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 

  • November 7, 2005 at 11:14 am

    #602646

    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

  • November 7, 2005 at 6:55 pm

    #602714

    Thank you Chris

    Bhushan

  • November 8, 2005 at 9:46 am

    #602815

    I do something similar. See:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=7&messageid=225752

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

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