Post reply

replication

  • April 11, 2014 at 8:48 am

    #396587

    i need to drop replication on some tables , how can i do ?

  • April 11, 2014 at 9:06 am

    #1705355

    On the publisher, you can run:use [PublishedDB]

    GO

    EXEC sp_dropsubscription @publication= N'PublicationName', @article =N'TableName', @subscriber=N'all'

    EXEC sp_droparticle @publication = N'PublicationName', @article = N'TableName', @force_invalidate_snapshot = 1

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • April 11, 2014 at 9:36 am

    #1705375

    Thanks for the reply is this query is defined to drop only one article , if yes what is the ysntax to drop multiple articles on publisher ?

  • April 11, 2014 at 10:11 am

    #1705404

    use [PublishedDB]

    GO

    EXEC sp_dropsubscription @publication= N'PublicationName', @article =N'Table1', @subscriber=N'all'

    EXEC sp_droparticle @publication = N'PublicationName', @article = N'Table1', @force_invalidate_snapshot = 1

    use [PublishedDB]

    GO

    EXEC sp_dropsubscription @publication= N'PublicationName', @article =N'Table2', @subscriber=N'all'

    EXEC sp_droparticle @publication = N'PublicationName', @article = N'Table2', @force_invalidate_snapshot = 1

    use [PublishedDB]

    GO

    EXEC sp_dropsubscription @publication= N'PublicationName', @article =N'Table3', @subscriber=N'all'

    EXEC sp_droparticle @publication = N'PublicationName', @article = N'Table3', @force_invalidate_snapshot = 1

    Etc...

    But if you want to automate it you could use some dynamic SQL:DECLARE @idx int = 1, @Table varchar(100), @sql varchar(500), @Database varchar(25), @PubName varchar(50)

    DECLARE @Tables TABLE (idx int IDENTITY(1,1), TName varchar(100))

    INSERT INTO @Tables

    SELECT 'Table1'

    UNION A-LL SELECT 'Table2'

    UNION A-LL SELECT 'Table3'

    SET @Database = 'YourDatabase'

    --Needs to be run on the distribution server

    SELECT @PubName = publication FROM Distribution.dbo.MSPublications WHERE publisher_db = @Database

    WHILE (SELECT COUNT(1) FROM @Tables) > 0

    BEGIN

    SELECT TOP 1 @Table = TName FROM @Tables WHERE idx = @idx

    SET @sql = 'USE [' + @Database +']' + CHAR(10) + 'GO' + CHAR(10)

    + 'EXEC sp_dropsubscription @publication= N''' + @PubName + ''', @article =N''' + @Table + ''', @subscriber=N''all''' + CHAR(10)

    + 'EXEC sp_droparticle @publication = N''' + @PubName + ''', @article = N''' + @Table + ''', @force_invalidate_snapshot = 1'

    PRINT @sql

    -- EXEC (@Sql)

    DELETE FROM @Tables WHERE idx = @idx

    SET @idx = @idx + 1

    END

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • April 11, 2014 at 10:22 am

    #1705411

    Thanks for the queries , really needful

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

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