Share via

  • Article

sp_articlefilter (Transact-SQL)

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

Topic link iconTransact-SQL Syntax Conventions

Syntax

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

Arguments

  • [ @publication=] 'publication'
    Is the name of the publication that contains the article. publication is sysname, with no default.

  • [ @article=] 'article'
    Is the name of the article. article is sysname, with no default.

  • [ @filter_name=] 'filter_name'
    Is the name of the filter stored procedure to be created from the filter_name. filter_name is nvarchar(386), with a default of NULL. You must specify a unique name for the article filter.

  • [ @filter_clause=] 'filter_clause'
    Is a restriction (WHERE) clause that defines a horizontal filter. When entering the restriction clause, omit the keyword WHERE. filter_clause is ntext, with a default of NULL.

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

    0 specifies that changes to the article do not 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 may 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 may require existing subscriptions to be reinitialized. force_reinit_subscription is a bit, with a default of 0.

    0 specifies that changes to the article do not 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 causes existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.

  • [ @publisher= ] 'publisher'
    Specifies a non-Microsoft SQL Server Publisher. publisher is sysname, with a default of NULL.

    Note

    publisher should not 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 (Transact-SQL) 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 (Transact-SQL) with no filter parameter. Execute sp_articlefilter, providing all parameters including filter_clause, and then execute sp_articleview (Transact-SQL), 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 are not provided, the previous filter is deleted and the filter ID is set to 0.

Example

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.