Change Article FILTER -- Where Clause -- in transactional replication

• Express12

  SSCoach

  Points: 18514

  More actions

  March 7, 2013 at 5:53 am

  #394331

  Looking for best way to accomplish this. I need to simply change the Horizontal FILTER / WHERE CLAUSE to include a few additional filter values in a SINGLE transactionally replicated article.

  My publication consists of over 100 articles, some of them very large so I'd like to avoid regenerating the entire snapshot.

  - Should I DROP the existing object via EXEC sp_dropsubscription to drop article in the subscription, then DROP the article in the publication by UN-Checking the box --then Re-Add the article w/ the new Horizontal FILTER?

  or

  - Should I use EXEC sp_changearticle to CHANGE the existing article?

  If anyone has a solid, tested recommendation and perhaps a sample script to attach, it would be very much appreciated! thx in advance

  BT

• Express12

  SSCoach

  Points: 18514

  More actions

  March 10, 2013 at 9:15 am

  #1595305

  -- This 4 step resolution worked:

  --************************************************

  -- SQL 2008 R2 -- Change an Article's Horizontal Row Filter

  --************************************************

  /**************

  4 steps required to alter this Articles Horizontal row filter:

  1. Drop article from any dependant downstream subscription(s)

  2. Drop article on Publisher (with @force_invalidate_snapshot = 1)

  3. Re-add the article w/ new Filter

  4. Run the snapshot agent

  --STEP 1 -- Drop article from dependant subscription(s)

  --

  -- Execute this at the PUBLISHER....

  --

  USE [myDB]

  DECLARE @publication AS sysname;

  DECLARE @subscriber AS sysname;

  DECLARE @article AS sysname;

  SET @publication = N'myPublication Name';

  SET @subscriber = N'mySubscriptionServerName';

  SET @article = N'myArticleName';

  EXEC sp_dropsubscription @publication = @publication, @subscriber = @subscriber, @article = @article;

  GO

  --STEP 2 -- Drop article on Publisher

  --

  -- Execute this at the PUBLISHER.... (key is to set @force_invalidate_snapshot = 1 !!!)

  --

  USE [myDB]

  DECLARE @publication AS sysname;

  DECLARE @article AS sysname;

  SET @publication = N'myPublication Name';

  SET @article = N'myArticleName';

  -- Drop the transactional article.

  USE [myDB]

  EXEC sp_droparticle

  @publication = @publication,

  @article = @article,

  @force_invalidate_snapshot = 1;

  GO

  -- STEP 3) re-add the article w/ new Filter - by bringing up the GUI and checking the article then adding it's NEW FILTER predicate

  --

  -- Article FILTER CLAUSE criteria:

  -- ==============================

  --

  -- Keystone AcctCorp:

  -- WHERE AcctCorp in (1603,1622,1692,1711,5818,9547,9549,9560)

  --

  - ADD TO REPLICATION

  - Click on NED Minerva Publication: 'myPublication Name'

  - Properties

  - Go to filter rows

  - Copy acctcorp in (…)

  - Go to articles

  - Uncheck show only checked objects

  - Find new table check the table

  - Go to Filter Rows

  - Add Filter

  - Paste acctcorp clause at the end

  - Click OK and OK

  - Go to Replication Monitor

  - Click on Publication

  - Warnings & Agent Tab

  - Right Click Sanpshot Agent

  - Select Start Agent

  - Select Subscription

  - All Subscriptions Tab

  - Double click to see if table is replicating

  -- STEP 4) run the snapshot agent - to create the article (which you just checked) on the subscriber

  ***************/

  BT