How to: Define a Logical Record Relationship Between Merge Table Articles (Replication Transact-SQL Programming)

Article
12/03/2008

Merge replication allows you to define a relationship between related rows in different tables. This means that rows can be processed as a transactional unit during synchronization. A logical record can be defined between two articles whether or not they have a join filter relationship. For more information, see Grouping Changes to Related Rows with Logical Records. You can programmatically specify logical record relationships between articles using replication stored procedures.

To define a logical record relationship without an associated join filter

If the publication contains any articles that are filtered, execute sp_helpmergepublication, and note the value of use_partition_groups in the result set.
- If the value is 1, then precomputed partitions are already being used.
- If the value is 0, then execute sp_changemergepublication at the Publisher on the publication database. Specify a value of use_partition_groups for @property and a value of true for @value.
  
  Note
  
  If the publication does not support precomputed partitions, then logical records cannot be used. For more information, see Requirements for Using Precomputed Partitions in the topic Optimizing Parameterized Filter Performance with Precomputed Partitions.
- If the value is NULL, then the Snapshot Agent needs to be run to generate the initial snapshot for the publication.
If the articles that will comprise the logical record do not exist, execute sp_addmergearticle at the Publisher on the publication database. Specify one of the following conflict detection and resolution options for the logical record:
- To detect and resolve conflicts that occur within related rows in the logic record, specify a value of true for @logical_record_level_conflict_detection and @logical_record_level_conflict_resolution.
- To use the standard row- or column-level conflict detection and resolution, specify a value of false for @logical_record_level_conflict_detection and @logical_record_level_conflict_resolution, which is the default.
Repeat step 2 for each article that will comprise the logical record. You must use the same conflict detection and resolution option for each article in the logical record. For more information, see Detecting and Resolving Conflicts in Logical Records.
At the publisher on the publication database, execute sp_addmergefilter. Specify @publication, the name of one article in the relationship for @article, the name of the second article for @join_articlename, a name for the relationship for @filtername, a clause that defines the relationship between the two articles for @join_filterclause, the type of join for @join_unique_key and one of the following values for @filter_type:
- 2 - Defines a logical relationship.
- 3 - Defines a logical relationship with a join filter.
Note

If a join filter is not used, the direction of the relationship between the two articles is not important.
Repeat step 2 for each remaining logical record relationship in the publication.

To change conflict detection and resolution for logical records

To detect and resolve conflicts that occur within related rows in the logical record:
- At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of logical_record_level_conflict_detection for @property and a value of true for @value. Specify a value of 1 for @force_invalidate_snapshot and @force_reinit_subscription.
- At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of logical_record_level_conflict_resolution for @property and a value of true for @value. Specify a value of 1 for @force_invalidate_snapshot and @force_reinit_subscription.
To use the standard row-level or column-level conflict detection and resolution:
- At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of logical_record_level_conflict_detection for @property and a value of false for @value. Specify a value of 1 for @force_invalidate_snapshot and @force_reinit_subscription.
- At the Publisher on the publication database, execute sp_changemergearticle. Specify a value of logical_record_level_conflict_resolution for @property and a value of false for @value. Specify a value of 1 for @force_invalidate_snapshot and @force_reinit_subscription.

To remove a logical record relationship

At the Publisher on the publication database, execute the following query to return information about all logical record relationships defined for the specified publication:
```
SELECT f.* FROM sysmergesubsetfilters AS f 
INNER JOIN sysmergepublications AS p
ON f.pubid = p.pubid WHERE p.[name] = @publication;
```
Note the name of the logical record relationship being removed in the filtername column in the result set.

Note

This query returns the same information as sp_helpmergefilter; however, this system stored procedure only returns information about logical record relationships that are also join filters.
At the Publisher on the publication database, execute sp_dropmergefilter. Specify @publication, the name of one of the articles in the relationship for @article, and the name of the relationship from step 1 for @filtername.

Example

This example enables precomputed partitions on an existing publication, and creates a logical record comprising the two new articles for the SalesOrderHeader and SalesOrderDetail tables.

-- Remove ON DELETE CASCADE from FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID;
-- logical records cannot be used with ON DELETE CASCADE. 
IF EXISTS (SELECT * FROM sys.objects 
WHERE name = 'FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID')
BEGIN
    ALTER TABLE [Sales].[SalesOrderDetail] 
    DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] 
END

ALTER TABLE [Sales].[SalesOrderDetail]  
WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID] 
FOREIGN KEY([SalesOrderID])
REFERENCES [Sales].[SalesOrderHeader] ([SalesOrderID])
GO

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);
DECLARE @partitionoption AS bit;
SET @publication = N'AdvWorksSalesOrdersMerge'; 
SET @table1 = N'SalesOrderDetail'; 
SET @table2 = N'SalesOrderHeader'; 
SET @salesschema = N'Sales';
SET @hrschema = N'HumanResources';
SET @filterclause = N'Employee.LoginID = HOST_NAME()';

-- Ensure that the publication uses precomputed partitions.
SET @partitionoption = (SELECT [use_partition_groups] FROM sysmergepublications 
    WHERE [name] = @publication);
IF @partitionoption <> 1
BEGIN
    EXEC sp_changemergepublication 
        @publication = @publication, 
        @property = N'use_partition_groups', 
        @value = 'true',
        @force_invalidate_snapshot = 1;
END  

-- 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.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_object = @table2, 
  @type = N'table', 
  @source_owner = @salesschema,
  @schema_option = 0x0034EF1,
  @description = N'article for the SalesOrderHeader table';

-- Add an article for the SalesOrderDetail table.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table3, 
  @source_object = @table3, 
  @source_owner = @salesschema,
  @description = 'article for the SalesOrderDetail table', 
  @identityrangemanagementoption = N'auto', 
  @pub_identity_range = 100000, 
  @identity_range = 100, 
  @threshold = 80;

-- 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;

-- Create a logical record relationship that is also a merge join 
-- filter between SalesOrderHeader and SalesOrderDetail.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table3, 
  @filtername = N'LogicalRecord_SalesOrderHeader_SalesOrderDetail', 
  @join_articlename = @table2, 
  @join_filterclause = N'[SalesOrderHeader].[SalesOrderID] = [SalesOrderDetail].[SalesOrderID]', 
  @join_unique_key = 1, 
  @filter_type = 3, 
  @force_invalidate_snapshot = 1, 
  @force_reinit_subscription = 1;
GO

How to: Define a Logical Record Relationship Between Merge Table Articles (Replication Transact-SQL Programming)

To define a logical record relationship without an associated join filter

To change conflict detection and resolution for logical records

To remove a logical record relationship

Example

See Also

Other Resources

Help and Information

Additional resources