sp_articlefilter (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Filters data that is published based on a table article. This stored procedure is executed at the Publisher on the publication database.

Transact-SQL syntax conventions

Syntax

sp_articlefilter
    [ @publication = ] N'publication'
    , [ @article = ] N'article'
    [ , [ @filter_name = ] N'filter_name' ]
    [ , [ @filter_clause = ] N'filter_clause' ]
    [ , [ @force_invalidate_snapshot = ] force_invalidate_snapshot ]
    [ , [ @force_reinit_subscription = ] force_reinit_subscription ]
    [ , [ @publisher = ] N'publisher' ]
[ ; ]

Arguments

[ @publication = ] N'publication'

The name of the publication that contains the article. @publication is sysname, with no default.

[ @article = ] N'article'

The name of the article. @article is sysname, with no default.

[ @filter_name = ] N'filter_name'

The name of the filter stored procedure to be created from the @filter_name. @filter_name is nvarchar(517), with a default of NULL. You must specify a unique name for the article filter.

[ @filter_clause = ] N'filter_clause'

A restriction (WHERE) clause that defines a horizontal filter. When entering the restriction clause, omit the keyword WHERE. @filter_clause is nvarchar(max), with a default of NULL.

[ @force_invalidate_snapshot = ] force_invalidate_snapshot

Acknowledges that the action taken by this stored procedure might invalidate an existing snapshot. @force_invalidate_snapshot is bit, with a default of 0.

  • 0 specifies that changes to the article don't cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error occurs and no changes are made.
  • 1 specifies that changes to the article might cause the snapshot to be invalid, and if there are existing subscriptions that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot generated.

[ @force_reinit_subscription = ] force_reinit_subscription

Acknowledges that the action taken by this stored procedure might require existing subscriptions to be reinitialized. @force_reinit_subscription is bit, with a default of 0.

  • 0 specifies that changes to the article don't cause a need for subscriptions to be reinitialized. If the stored procedure detects that the change would require subscriptions to be reinitialized, an error occurs and no changes are made.
  • 1 specifies that changes to the article cause existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.

[ @publisher = ] N'publisher'

Specifies a non-SQL Server Publisher. @publisher is sysname, with a default of NULL.

@publisher shouldn't be used with a SQL Server Publisher.

Return code values

0 (success) or 1 (failure).

Remarks

sp_articlefilter is used in snapshot replication and transactional replication.

Executing sp_articlefilter for an article with existing subscriptions requires that those subscriptions to be reinitialized.

sp_articlefilter creates the filter, inserts the ID of the filter stored procedure in the filter column of the sysarticles table, and then inserts the text of the restriction clause in the filter_clause column.

To create an article with a horizontal filter, execute sp_addarticle with no @filter_name parameter. Execute sp_articlefilter, providing all parameters including @filter_clause, and then execute sp_articleview, providing all parameters including the identical @filter_clause. If the filter already exists and if the type in sysarticles is 1 (log-based article), the previous filter is deleted and a new filter is created.

If @filter_name and @filter_clause aren't provided, the previous filter is deleted and the filter ID is set to 0.

Examples

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

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_articlefilter.

Related content