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' ]
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