How to: Define and Modify a Column Filter (Replication Transact-SQL Programming)

Article
12/03/2008

When creating table articles, you can define which columns to include in the article and change the columns after the article has been defined. You can create and modify filtered columns programmatically using replication stored procedures. Some types of columns, such as identity and rowguid columns, cannot be removed from a published article. For more information, see Column Filters in the topic Filtering Published Data.

Note

The following procedures assume that the underlying table has not changed. For information on replicating data definition language (DDL) changes to published tables, see Making Schema Changes on Publication Databases.

To define a column filter for an article published in a snapshot or transactional publication

Define the article to filter. For more information, see How to: Define an Article (Replication Transact-SQL Programming).
At the Publisher on the publication database, execute sp_articlecolumn. This defines the columns to include or remove from the article.
- If publishing only a few columns from a table with many columns, execute sp_articlecolumn once for each column being added. Specify the column name for @column and a value of add for @operation.
- If publishing most of the columns in a table with many columns, execute sp_articlecolumn, specifying a value of null for @column and a value of add for @operation to add all columns. Then execute sp_articlecolumn, once for each column being excluded, specifying a value of drop for @operation and the excluded column name for @column.
At the Publisher on the publication database, execute sp_articleview. Specify the publication name for @publication and the name of the filtered article for @article. This creates the synchronization objects for the filtered article.

To change a column filter to include additional columns for an article published in a snapshot or transactional publication

At the Publisher on the publication database, execute sp_articlecolumn once for each column being added. Specify the column name for @column and a value of add for @operation.
At the Publisher on the publication database, execute sp_articleview. Specify the publication name for @publication and the name of the filtered article for @article. If the publication has existing subscriptions, specify a value of 1 for @change_active. This re-creates the synchronization objects for the filtered article.
Rerun the Snapshot Agent job for the publication to generate an updated snapshot.
Reinitialize subscriptions. For more information, see How to: Reinitialize a Subscription (Replication Transact-SQL Programming).

To change a column filter to remove columns for an article published in a snapshot or transactional publication

At the Publisher on the publication database, execute sp_articlecolumn once for each column being removed. Specify the column name for @column and a value of drop for @operation.
At the Publisher on the publication database, execute sp_articleview. Specify the publication name for @publication and the name of the filtered article for @article. If the publication has existing subscriptions, specify a value of 1 for @change_active. This re-creates the synchronization objects for the filtered article.
Rerun the Snapshot Agent job for the publication to generate an updated snapshot.
Reinitialize subscriptions. For more information, see How to: Reinitialize a Subscription (Replication Transact-SQL Programming).

To define a column filter for an article published in a merge publication

Define the article to filter. For more information, see How to: Define an Article (Replication Transact-SQL Programming).
At the Publisher on the publication database, execute sp_mergearticlecolumn. This defines the columns to include or remove from the article.
- If publishing only a few columns from a table with many columns, execute sp_mergearticlecolumn once for each column being added. Specify the column name for @column and a value of add for @operation.
- If publishing most of the columns in a table with many columns, execute sp_mergearticlecolumn, specifying a value of null for @column and a value of add for @operation to add all columns. Then execute sp_mergearticlecolumn, once for each column being excluded, specifying a value of drop for @operation and the excluded column name for @column.

To change a column filter to include additional columns for an article published in a merge publication

At the Publisher on the publication database, execute sp_mergearticlecolumn once for each column being added. Specify the column name for @column, a value of add for @operation and a value of 1 for both @force_invalidate_snapshot and @force_reinit_subscription.
Rerun the Snapshot Agent job for the publication to generate an updated snapshot.
Reinitialize subscriptions. For more information, see How to: Reinitialize a Subscription (Replication Transact-SQL Programming).

To change a column filter to remove columns for an article published in a merge publication

At the Publisher on the publication database, execute sp_mergearticlecolumn once for each column being removed. Specify the column name for @column, a value of drop for @operation and a value of 1 for both @force_invalidate_snapshot and @force_reinit_subscription.
Rerun the Snapshot Agent job for the publication to generate an updated snapshot.
Reinitialize subscriptions. For more information, see How to: Reinitialize a Subscription (Replication Transact-SQL Programming).

Example

In this transactional replication example, the DaysToManufacture column is removed from an article based on the Product table.

DECLARE @publication    AS sysname;
DECLARE @table AS sysname;
DECLARE @filterclause AS nvarchar(500);
DECLARE @filtername AS nvarchar(386);
DECLARE @schemaowner AS sysname;
SET @publication = N'AdvWorksProductTran'; 
SET @table = N'Product';
SET @filterclause = N'[DiscontinuedDate] IS NULL'; 
SET @filtername = N'filter_out_discontinued';
SET @schemaowner = N'Production';

-- Add a horizontally and vertically filtered article for the Product table.
-- Manually set @schema_option to ensure that the Production schema 
-- is generated at the Subscriber (0x8000000).
EXEC sp_addarticle 
    @publication = @publication, 
    @article = @table, 
    @source_object = @table,
    @source_owner = @schemaowner, 
    @schema_option = 0x80030F3,
    @vertical_partition = N'true', 
    @type = N'logbased',
    @filter_clause = @filterclause;

-- (Optional) Manually call the stored procedure to create the 
-- horizontal filtering stored procedure. Since the type is 
-- 'logbased', this stored procedures is executed automatically.
EXEC sp_articlefilter 
    @publication = @publication, 
    @article = @table, 
    @filter_clause = @filterclause, 
    @filter_name = @filtername;

-- Add all columns to the article.
EXEC sp_articlecolumn 
    @publication = @publication, 
    @article = @table;

-- Remove the DaysToManufacture column from the article
EXEC sp_articlecolumn 
    @publication = @publication, 
    @article = @table, 
    @column = N'DaysToManufacture', 
    @operation = N'drop';

-- (Optional) Manually call the stored procedure to create the 
-- vertical filtering view. Since the type is 'logbased', 
-- this stored procedures is executed automatically.
EXEC sp_articleview 
    @publication = @publication, 
    @article = @table,
    @filter_clause = @filterclause;
GO

In this merge replication example, the CreditCardApprovalCode column is removed from an article based on the SalesOrderHeader table.

DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @table3 AS sysname;
DECLARE @salesschema AS sysname;
DECLARE @hrschema AS sysname;
DECLARE @filterclause AS nvarchar(1000);
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @table1 = N'Employee'; 
SET @table2 = N'SalesOrderHeader'; 
SET @table3 = N'SalesOrderDetail'; 
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';

-- Add a filtered article for the Employee table.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_object = @table1, 
  @type = N'table', 
  @source_owner = @hrschema,
  @schema_option = 0x0004CF1,
  @description = N'article for the Employee table',
  @subset_filterclause = @filterclause;

-- Add an article for the SalesOrderHeader table that is filtered
-- based on Employee and horizontally filtered.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_object = @table2, 
  @type = N'table', 
  @source_owner = @salesschema, 
  @vertical_partition = N'true',
  @schema_option = 0x0034EF1,
  @description = N'article for the SalesOrderDetail table';

-- Add an article for the SalesOrderDetail table that is filtered
-- based on SaledOrderHeader.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table3, 
  @source_object = @table3, 
  @source_owner = @salesschema,
  @description = 'article for the SalesOrderHeader table', 
  @identityrangemanagementoption = N'auto', 
  @pub_identity_range = 100000, 
  @identity_range = 100, 
  @threshold = 80,
  @schema_option = 0x0004EF1;

-- Add all columns to the SalesOrderHeader article.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Remove the credit card Approval Code column.
EXEC sp_mergearticlecolumn 
  @publication = @publication, 
  @article = @table2, 
  @column = N'CreditCardApprovalCode', 
  @operation = N'drop', 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between Employee and SalesOrderHeader.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table2, 
  @filtername = N'SalesOrderHeader_Employee', 
  @join_articlename = @table1, 
  @join_filterclause = N'Employee.EmployeeID = SalesOrderHeader.SalesPersonID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;

-- Add a merge join filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table3, 
  @filtername = N'SalesOrderDetail_SalesOrderHeader', 
  @join_articlename = @table2, 
  @join_filterclause = N'SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID', 
  @join_unique_key = 1, 
  @filter_type = 1, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;
GO

Share via

How to: Define and Modify a Column Filter (Replication Transact-SQL Programming)

To define a column filter for an article published in a snapshot or transactional publication

To change a column filter to include additional columns for an article published in a snapshot or transactional publication

To change a column filter to remove columns for an article published in a snapshot or transactional publication

To define a column filter for an article published in a merge publication

To change a column filter to include additional columns for an article published in a merge publication

To change a column filter to remove columns for an article published in a merge publication

Example

See Also

Other Resources

Help and Information

Additional resources