Post reply

deleting an article from replication

  • May 31, 2008 at 12:27 pm

    #188490

    I want to drop a table.. but it is being used for replication so I can't drop it. How do I drop the article from replication????

    this is sql server 2000 sp4

  • June 1, 2008 at 6:15 am

    #822720

    sp_droparticle for transactional and snapshot, sp_dropmergearticle for merge replication. check out the syntax for both in books online.

  • June 1, 2008 at 6:33 am

    #822721

    awesome. thank you

  • June 2, 2008 at 3:26 pm

    #823333

    You will also need to remove all subscriptions first. Per BOL:

    An article cannot be removed if one or more subscriptions to it exist. This stored procedure (sp_droparticle) is executed at the Publisher on the publication database.

  • June 2, 2008 at 3:47 pm

    #823344

    It goes like this:

    Exec dbo.sp_dropsubscription @publication='publication_name', @article='object_to_drop', @subscriber='SUBSCRIBER_SERVER_NAME'

    Exec dbo.sp_droparticle @publication='publication_name', @article='object_to_drop',

    @force_invalidate_snapshot=1

    Exec dbo.sp_refreshsubscriptions 'publication_name'


    * Noel

  • April 26, 2009 at 3:42 pm

    #984135

    However; you have to loop through all of the Subscriptions first and run the sp_DropSubscription for each of the Subscriptions and then run the sp_DropArticle at the end only once for the Publication.

Viewing 6 posts - 1 through 5 (of 5 total)

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