none
delete table in publisher and it is not replicated in subscriber RRS feed

  • Question

  • I am using transactional replication. these are the steps I followed to deleted a replicated tables in the publisher.

    1. Rt click publisher properties and unchecked the table in the articles.

    2. Deleted table in the database 1.

    3. Then I went to subscriptions tab and then hit reinitialize

    4. Then I went back to the snapshot agent and start the agent.

    But the table which doesnt exist in the publisher Database 1 is still available in the subscription Database 2.

    Did I did something wrong?

    Thanks

     


    BRP
    Monday, December 13, 2010 4:15 PM

Answers

  • I am somewhat confused about your request. If you say you drop a table from a publication do you expect that table to be removed at the subscriber?

    Customer demand and replication design is that objects which are replicated to a subscriber remain there after replication is removed or a table/article is dropped from a publication.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, December 16, 2010 1:15 PM
    Moderator

All replies

  • Deleting a table from a publication does not drop it on the subscriber.

    In the future all you should really have to do is issue a sp_dropsubscription and pass the article name in the @articles parameter.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Monday, December 13, 2010 4:19 PM
    Moderator
  • Thank you Cotter for the answer.. But how do I implement it...directly run the stored procedure . I did the below and it didnot work. I want to remove the table in publisher and it shouldnt be there in the subscriber. Can you please suggest

     

    DECLARE @publication AS sysname;
    DECLARE @subscriber AS sysname;
    SET @publication = N'Test_Source_Publication';
    SET @subscriber = N'Test_Subscriber';

    use Test_Source
    EXEC sp_dropsubscription
      @publication = @publication,
      @article = N'Products',
      @subscriber = @subscriber;
    GO


    BRP
    Monday, December 13, 2010 6:50 PM
  • Hey my big question is it doesnt delete table even after u reinit the subscriber with new snapshot created?? U shouldnt be seeing table after u generate new snapshot and reinit
    Wednesday, December 15, 2010 6:19 PM
  • I am somewhat confused about your request. If you say you drop a table from a publication do you expect that table to be removed at the subscriber?

    Customer demand and replication design is that objects which are replicated to a subscriber remain there after replication is removed or a table/article is dropped from a publication.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Thursday, December 16, 2010 1:15 PM
    Moderator