sp_articlefilter Replication Stored Procedure

Creates a filter stored procedure used to horizontally partition data replicated from a published table.

Syntax

sp_articlefilter publication, article [, filter_name] [, filter_clause]

where

publication

Is the name of the publication that contains the article.

article

Is the name of the article.

filter_name

Is the name of the filter stored procedure to be created from the filter_clause.

filter_clause

Is a restriction (WHERE) clause that defines a horizontal partition. When entering the restriction clause, omit the word "WHERE."

Remarks

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

Only unsubscribed articles can be modified by this stored procedure.

To create an article with a horizontal partition, execute sp_addarticle with no filter parameter. Execute sp_articlefilter, providing all parameters including filter_clause. 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) or 3 (log-based article with manual filter), the filter will be dropped and re-created.

If filter_name and filter_clause are not provided, the previous filter is deleted and the filter ID is set to 0.

Example

This example creates the mypub filtered stored procedure.

sp_articlefilter, MyPub, MyArticle, MyFilter, 'state = "OR"'

Permission

Only the system administrator and the database owner can use this stored procedure.

Tables Used

sysobjects, syspublications, syssubscriptions, sysarticles

See Also