Transact-SQL Reference

sp_articlefilter

Filters data that will be published based on a table article. Only articles without subscriptions can be modified by this stored procedure. This stored procedure is executed at the Publisher on the publication database.

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 ]

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.

[@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 will not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error will occur and no changes will be 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 will 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 will occur and no changes will be made. 1 specifies that changes to the article will cause existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.

Return Code Values

0 (success) or 1 (failure)

Remarks

sp_article_filter is used in snapshot replication and transactional replication.

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 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 are not provided, the previous filter is deleted and the filter ID is set to 0.

Permissions