Define a Logical Record Relationship Between Merge Table Articles
Applies to: 
SQL Server
This topic describes how to define a logical record relationship between merge table articles in SQL Server by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).
Merge replication allows you to define a relationship between related rows in different tables. These rows can then 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 Group Changes to Related Rows with Logical Records.
Note
This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
In This Topic
Before you begin:
To define a logical record relationship between merge table articles, using:
Before You Begin
Limitations and Restrictions
- If you add, modify, or delete a logical record after subscriptions to the publication have been initialized, you must generate a new snapshot and reinitialize all subscriptions after making the change. For more information about requirements for property changes, see Change Publication and Article Properties.
Using SQL Server Management Studio
Define logical records in the Add Join dialog box, which is available in the New Publication Wizard and the Publication Properties - <Publication> dialog box. For more information about using the wizard and accessing the dialog box, see Create a Publication and View and Modify Publication Properties.
Logical records can be defined in the Add Join dialog box only if they are applied to a join filter in a merge publication, and the publication follows the requirements for using precomputed partitions. To define logical records that are not applied to join filters and to set conflict detection and resolution at the logical record level, you must use stored procedures.
To define a logical record relationship
On the Filter Table Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box, select a row filter in the Filtered Tables pane.
A logical record relationship is associated with a join filter, which extends a row filter. Therefore you must define a row filter before you can extend the filter with a join and apply a logical record relationship. After one join filter is defined, you can extend this join filter with another join filter. For more information about defining join filters, see Define and Modify a Join Filter Between Merge Articles.
Click Add, and then click Add Join to Extend the Selected Filter.
Define a join filter in the Add Join dialog box, and then select the check box Logical Record.
If you are in the Publication Properties - <Publication> dialog box, click OK to save and close the dialog box.
To delete a logical record relationship
Delete only the logical record relationship or delete the logical record relationship and the join filter associated with it.
To delete only the logical record relationship:
On the Filter Rows page of the New Publication Wizard or the Filter Rows page of the Publication Properties - <Publication> dialog box, select the join filter associated with the logical record relationship in the Filtered Tables pane, and then click Edit.
In the Edit Join dialog box, clear the check box Logical Record.
Select OK.
To delete the logical record relationship and join filter associated with it:
- On the Filter Rows page of the New Publication Wizard or Publication Properties - <Publication> dialog box, select a filter in the Filtered Tables pane, and then click Delete. If the join filter you delete is itself extended by other joins, those joins will also be deleted.
Using Transact-SQL
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 Optimize 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 (Transact-SQL)
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
Using Replication Management Objects (RMO)
Note
Merge replication allows you to specify that conflicts be tracked and resolved at the logical record level, but these options cannot be set using RMO.
To define a logical record relationship without an associated join filter
Create a connection to the Publisher by using the ServerConnection class.
Create an instance of the MergePublication class, set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the connection created in step 1.
Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.
If the PartitionGroupsOption property is set to False, set it to True.
If the articles that are to comprise the logical record do not exist, create an instance of the MergeArticle class, and set the following properties:
The name of the article for Name.
The name of the publication for PublicationName.
(Optional) If the article is horizontally filtered, specify the row filter clause for the FilterClause property. Use this property to specify a static or parameterized row filter. For more information, see Parameterized Row Filters.
For more information, see Define an Article.
Call the Create method.
Repeat steps 5 and 6 for each article comprising the logical record.
Create an instance of the MergeJoinFilter class to define the logical record relationship between articles. Then, set the following properties:
The name of the child article in the logical record relationship for the ArticleName property.
The name of the existing, parent article in the logical record relationship for the JoinArticleName property.
A name for the logical record relationship for the FilterName property.
The expression that defines the relationship for the JoinFilterClause property.
A value of LogicalRecordLink for the FilterTypes property. If the logical record relationship is also a join filter, specify a value of JoinFilterAndLogicalRecordLink for this property. For more information, see Group Changes to Related Rows with Logical Records.
Call the AddMergeJoinFilter method on the object that represents the child article in the relationship. Pass the MergeJoinFilter object from step 8 to define the relationship.
Repeat steps 8 and 9 for each remaining logical record relationship in the publication.
Example (RMO)
This example creates a logical record comprising the two new articles for the SalesOrderHeader and SalesOrderDetail tables.
// Define the Publisher and publication names.
string publisherName = publisherInstance;
string publicationName = "AdvWorksSalesOrdersMerge";
string publicationDbName = "AdventureWorks2022";
// Specify article names.
string articleName1 = "SalesOrderHeader";
string articleName2 = "SalesOrderDetail";
// Specify logical record information.
string lrName = "SalesOrderHeader_SalesOrderDetail";
string lrClause = "[SalesOrderHeader].[SalesOrderID] = "
+ "[SalesOrderDetail].[SalesOrderID]";
string schema = "Sales";
MergeArticle article1 = new MergeArticle();
MergeArticle article2 = new MergeArticle();
MergeJoinFilter lr = new MergeJoinFilter();
MergePublication publication = new MergePublication();
// Create a connection to the Publisher.
ServerConnection conn = new ServerConnection(publisherName);
try
{
// Connect to the Publisher.
conn.Connect();
// Verify that the publication uses precomputed partitions.
publication.Name = publicationName;
publication.DatabaseName = publicationDbName;
publication.ConnectionContext = conn;
// If we can't get the properties for this merge publication, then throw an application exception.
if (publication.LoadProperties())
{
// If precomputed partitions is disabled, enable it.
if (publication.PartitionGroupsOption == PartitionGroupsOption.False)
{
publication.PartitionGroupsOption = PartitionGroupsOption.True;
}
}
else
{
throw new ApplicationException(String.Format(
"Settings could not be retrieved for the publication. " +
"Ensure that the publication {0} exists on {1}.",
publicationName, publisherName));
}
// Set the required properties for the PurchaseOrderHeader article.
article1.ConnectionContext = conn;
article1.Name = articleName1;
article1.DatabaseName = publicationDbName;
article1.SourceObjectName = articleName1;
article1.SourceObjectOwner = schema;
article1.PublicationName = publicationName;
article1.Type = ArticleOptions.TableBased;
// Set the required properties for the SalesOrderDetail article.
article2.ConnectionContext = conn;
article2.Name = articleName2;
article2.DatabaseName = publicationDbName;
article2.SourceObjectName = articleName2;
article2.SourceObjectOwner = schema;
article2.PublicationName = publicationName;
article2.Type = ArticleOptions.TableBased;
if (!article1.IsExistingObject) article1.Create();
if (!article2.IsExistingObject) article2.Create();
// Define a logical record relationship between
// PurchaseOrderHeader and PurchaseOrderDetail.
// Parent article.
lr.JoinArticleName = articleName1;
// Child article.
lr.ArticleName = articleName2;
lr.FilterName = lrName;
lr.JoinUniqueKey = true;
lr.FilterTypes = FilterTypes.LogicalRecordLink;
lr.JoinFilterClause = lrClause;
// Add the logical record definition to the parent article.
article1.AddMergeJoinFilter(lr);
}
catch (Exception ex)
{
// Do error handling here and rollback the transaction.
throw new ApplicationException(
"The filtered articles could not be created", ex);
}
finally
{
conn.Disconnect();
}
' Define the Publisher and publication names.
Dim publisherName As String = publisherInstance
Dim publicationName As String = "AdvWorksSalesOrdersMerge"
Dim publicationDbName As String = "AdventureWorks2022"
' Specify article names.
Dim articleName1 As String = "SalesOrderHeader"
Dim articleName2 As String = "SalesOrderDetail"
' Specify logical record information.
Dim lrName As String = "SalesOrderHeader_SalesOrderDetail"
Dim lrClause As String = "[SalesOrderHeader].[SalesOrderID] = " _
& "[SalesOrderDetail].[SalesOrderID]"
Dim schema As String = "Sales"
Dim article1 As MergeArticle = New MergeArticle()
Dim article2 As MergeArticle = New MergeArticle()
Dim lr As MergeJoinFilter = New MergeJoinFilter()
Dim publication As MergePublication = New MergePublication()
' Create a connection to the Publisher.
Dim conn As ServerConnection = New ServerConnection(publisherName)
Try
' Connect to the Publisher.
conn.Connect()
' Verify that the publication uses precomputed partitions.
publication.Name = publicationName
publication.DatabaseName = publicationDbName
publication.ConnectionContext = conn
' If we can't get the properties for this merge publication, then throw an application exception.
If publication.LoadProperties() Then
' If precomputed partitions is disabled, enable it.
If publication.PartitionGroupsOption = PartitionGroupsOption.False Then
publication.PartitionGroupsOption = PartitionGroupsOption.True
End If
Else
Throw New ApplicationException(String.Format( _
"Settings could not be retrieved for the publication. " _
& "Ensure that the publication {0} exists on {1}.", _
publicationName, publisherName))
End If
' Set the required properties for the SalesOrderHeader article.
article1.ConnectionContext = conn
article1.Name = articleName1
article1.DatabaseName = publicationDbName
article1.SourceObjectName = articleName1
article1.SourceObjectOwner = schema
article1.PublicationName = publicationName
article1.Type = ArticleOptions.TableBased
' Set the required properties for the SalesOrderDetail article.
article2.ConnectionContext = conn
article2.Name = articleName2
article2.DatabaseName = publicationDbName
article2.SourceObjectName = articleName2
article2.SourceObjectOwner = schema
article2.PublicationName = publicationName
article2.Type = ArticleOptions.TableBased
If Not article1.IsExistingObject Then
article1.Create()
End If
If Not article2.IsExistingObject Then
article2.Create()
End If
' Define a logical record relationship between
' SalesOrderHeader and SalesOrderDetail.
' Parent article.
lr.JoinArticleName = articleName1
' Child article.
lr.ArticleName = articleName2
lr.FilterName = lrName
lr.JoinUniqueKey = True
lr.FilterTypes = FilterTypes.LogicalRecordLink
lr.JoinFilterClause = lrClause
' Add the logical record definition to the parent article.
article1.AddMergeJoinFilter(lr)
Catch ex As Exception
' Do error handling here and rollback the transaction.
Throw New ApplicationException( _
"The filtered articles could not be created", ex)
Finally
conn.Disconnect()
End Try