sp_addmergefilter (Transact-SQL)
Adds a new merge filter to create a partition based on a join with another table. This stored procedure is executed at the Publisher on the publication database.
.gif "Topic link icon")
Syntax
sp_addmergefilter [ @publication= ] 'publication', [ @article= ] 'article', [ @filtername= ] 'filtername', [ @join_articlename= ] 'join_articlename', [ @join_filterclause= ] join_filterclause
[ , [ @join_unique_key= ] join_unique_key ]
[ , [ @force_invalidate_snapshot= ] force_invalidate_snapshot ]
[ , [ @force_reinit_subscription= ] force_reinit_subscription ]
[ , [ @filter_type= ] filter_type ]
Arguments
[ @publication= ] 'publication'
Is the name of the publication in which the merge filter is being added. publication is sysname, with no default.[ @article= ] 'article'
Is the name of the article on which the merge filter is being added. article is sysname, with no default.[ @filtername= ] 'filtername'
Is the name of the filter. filtername is a required parameter. filternameis sysname, with no default.[ @join_articlename= ] 'join_articlename'
Is the parent article to which the child article, specified by article, must be joined using the join clause specified by join_filterclause, in order to determine the rows in the child article that meet the filter criterion of the merge filter. join_articlename is sysname, with no default. The article must be in the publication given by publication.[ @join_filterclause= ] join_filterclause
Is the join clause that must be used to join the child article specified by articleand parent article specified by join_article, in order to determine the rows qualifying the merge filter. join_filterclause is nvarchar(1000).[ @join_unique_key= ] join_unique_key
Specifies if the join between child article articleand parent article join_articleis one-to-many, one-to-one, many-to-one, or many-to-many. join_unique_key is int, with a default of 0. 0 indicates a many-to-one or many-to-many join. 1 indicates a one-to-one or one-to-many join. This value is 1 when the joining columns form a unique key in join_article, or if join_filterclause is between a foreign key in article and a primary key in join_article.Warning
Only set this parameter to 1 if you have a constraint on the joining column in the underlying table for the parent article that guarantees uniqueness. If join_unique_key is set to 1 incorrectly, non-convergence of data may occur.
[ @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 0.0 specifies that changes to the merge 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 merge 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 merge article will not cause the subscription 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 merge article will cause existing subscriptions to be reinitialized, and gives permission for the subscription reinitialization to occur.
[ @filter_type= ] filter_type
Specifies the type of filter being added. filter_type is tinyint, and can be one of the following values.Value
Description
1
Join filter only. Required to support SQL Server Compact 3.5 SP1 Subscribers.
2
Logical record relationship only.
3
Both join filter and logical record relationship.
For more information, see Grouping Changes to Related Rows with Logical Records.
Return Code Values
0 (success) or 1 (failure)
Remarks
sp_addmergefilter is used in merge replication.
sp_addmergefilter can only be used with table articles. View and indexed view articles are not supported.
This procedure can also be used to add a logical relationship between two articles that may or may not have a join filter between them. filter_type is used to specify if the merge filter being added is a join filter, a logical relation, or both.
To use logical records, the publication and articles must meet a number of requirements. For more information, see Grouping Changes to Related Rows with Logical Records.
Typically, this option is used for an article that has a foreign key reference to a published primary key table, and the primary key table has a filter defined in its article. The subset of primary key rows is used to determine the foreign key rows that are replicated to the Subscriber.
You cannot add a join filter between two published articles when the source tables for both articles share the same table object name. In such a case, even if both tables are owned by different schemas and have unique article names, creation of the join filter will fail.
When both a parameterized row filter and a join filter are used on a table article, replication determines whether a row belongs in a Subscriber's partition. It does so by evaluating either the filtering function or the join filter (using the OR operator), rather than evaluating the intersection of the two conditions (using the AND operator).
Example
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @table3 AS sysname;
DECLARE @salesschema AS sysname;
DECLARE @hrschema AS sysname;
DECLARE @filterclause AS nvarchar(1000);
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesOrderHeader';
SET @table3 = N'SalesOrderDetail';
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';
-- Add a filtered article for the Employee table.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table1,
@source_object = @table1,
@type = N'table',
@source_owner = @hrschema,
@schema_option = 0x0004CF1,
@description = N'article for the Employee table',
@subset_filterclause = @filterclause;
-- Add an article for the SalesOrderHeader table that is filtered
-- based on Employee and horizontally filtered.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table2,
@source_object = @table2,
@type = N'table',
@source_owner = @salesschema,
@vertical_partition = N'true',
@schema_option = 0x0034EF1,
@description = N'article for the SalesOrderDetail table';
-- Add an article for the SalesOrderDetail table that is filtered
-- based on SaledOrderHeader.
EXEC sp_addmergearticle
@publication = @publication,
@article = @table3,
@source_object = @table3,
@source_owner = @salesschema,
@description = 'article for the SalesOrderHeader table',
@identityrangemanagementoption = N'auto',
@pub_identity_range = 100000,
@identity_range = 100,
@threshold = 80,
@schema_option = 0x0004EF1;
-- Add all columns to the SalesOrderHeader article.
EXEC sp_mergearticlecolumn
@publication = @publication,
@article = @table2,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
-- Remove the credit card Approval Code column.
EXEC sp_mergearticlecolumn
@publication = @publication,
@article = @table2,
@column = N'CreditCardApprovalCode',
@operation = N'drop',
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
-- Add a merge join filter between Employee and SalesOrderHeader.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table2,
@filtername = N'SalesOrderHeader_Employee',
@join_articlename = @table1,
@join_filterclause = N'Employee.EmployeeID = SalesOrderHeader.SalesPersonID',
@join_unique_key = 1,
@filter_type = 1,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
-- Add a merge join filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter
@publication = @publication,
@article = @table3,
@filtername = N'SalesOrderDetail_SalesOrderHeader',
@join_articlename = @table2,
@join_filterclause = N'SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID',
@join_unique_key = 1,
@filter_type = 1,
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1;
GO
Permissions
Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_addmergefilter.
See Also