Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
When creating table articles, you can use parameterized row filters. These filters use a WHERE clause to select the appropriate data to be published. Rather than specifying a literal value in the clause (as you do with a static row filter), you specify one or both of the following system functions: SUSER_SNAME and HOST_NAME. For more information, see Parameterized Row Filters. Parameterized row filters can be created and modified programmatically using replication stored procedures.
To define a parameterized row filter for an article in a merge publication
At the Publisher on the publication database, execute sp_addmergearticle (Transact-SQL). Specify @publication, a name for the article for @article, the table being published for @source_object, the WHERE clause that defines the parameterized filter for @subset_filterclause (not including
WHERE), and one of the following values for @partition_options, which describes the type of partitioning that will result from the parameterized row filter:- 0 - Filtering for the article either is static or does not yield a unique subset of data for each partition (an "overlapping" partition).
- 1 - Resulting partitions are overlapping, and updates made at the Subscriber cannot change the partition to which a row belongs.
- 2 - Filtering for the article yields nonoverlapping partitions, but multiple Subscribers can receive the same partition.
- 3 - Filtering for the article yields nonoverlapping partitions that are unique for each subscription.
To change a parameterized row filter for an article in a merge publication
At the Publisher on the publication database, execute sp_changemergearticle. Specify @publication, @article, a value of subset_filterclause for @property, the expression that defines the parameterized filter for @value (not including
WHERE), and a value of 1 for both @force_invalidate_snapshot and @force_reinit_subscription.If this change results in different partitioning behavior, then execute sp_changemergearticle again. Specify @publication, @article, a value of partition_options for @property, and the most appropriate partitioning option for @value, which can be one of the following:
- 0 - Filtering for the article either is static or does not yield a unique subset of data for each partition (an "overlapping" partition).
- 1 - Resulting partitions are overlapping, and updates made at the Subscriber cannot change the partition to which a row belongs.
- 2 - Filtering for the article yields nonoverlapping partitions, but multiple Subscribers can receive the same partition.
- 3 - Filtering for the article yields nonoverlapping partitions that are unique for each subscription.
Example
This example defines a group of articles in a merge publication where the articles are filtered with a series of join filters against the Employee Table that is itself filtered using a parameterized row filter on the LoginID column. During synchronization, the value returned by the HOST_NAME function is overridden. For more information, see Overriding the HOST_NAME() Value in the topic Parameterized Row Filters.
-- To avoid storing the login and password in the script file, the value
-- is passed into SQLCMD as a scripting variable. For information about
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".
--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;
SET @publicationdb = N'AdventureWorks';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';
USE [AdventureWorks];
-- Enable AdventureWorks for merge replication.
EXEC sp_replicationdboption
@dbname = @publicationdb,
@optname = N'merge publish',
@value = N'true';
-- Create new merge publication with Subscriber requested snapshot
-- and using the default agent schedule.
EXEC sp_addmergepublication
@publication = @publication,
@description = N'Merge publication of AdventureWorks.',
@allow_subscriber_initiated_snapshot = N'true',
@publication_compatibility_level = N'90RTM';
-- Create a new snapshot job for the publication, using the default schedule.
-- Pass credentials at runtime using sqlcmd scripting variables.
EXEC sp_addpublication_snapshot
@publication = @publication,
@job_login = $(login),
@job_password = $(password);
-- Add an article for the Employee table,
-- which is horizontally partitioned using
-- a parameterized row filter.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table1,
@source_owner = @schema_hr,
@source_object = @table1,
@type = N'table',
@description = 'contains employee information',
@subset_filterclause = N'[LoginID] = HOST_NAME()';
-- Add an article for the SalesPerson table,
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table2,
@source_owner = @schema_sales,
@source_object = @table2,
@type = N'table',
@description = 'contains salesperson information';
-- Add a join filter between the two articles.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table1,
@filtername = @filter,
@join_articlename = @table2,
@join_filterclause = N'[Employee].[EmployeeID] = [SalesPerson].[SalesPersonID]',
@join_unique_key = 1,
@filter_type = 1;
GO
-- Start the agent job to generate the full snapshot for the publication.
-- The filtered data snapshot is generated automatically the first time
-- the subscription is synchronized.
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';
EXEC sp_startpublication_snapshot
@publication = @publication;
GO
See Also
Tasks
Other Resources
Changing Publication and Article Properties