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.
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' ]
- [ @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.
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.
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
