Export (0) Print
Expand All

How to: Define a Logical Record Relationship Between Merge Table Articles (RMO Programming)

New: 5 December 2005

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 Grouping Changes to Related Rows with Logical Records.

You can programmatically specify logical record relationships between articles using Replication Management Objects (RMO).

ms345205.note(en-US,SQL.90).gifNote:
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. For information about setting these options using replication stored procedures, see How to: Define a Logical Record Relationship Between Merge Table Articles (Replication Transact-SQL Programming).

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. 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.

  3. 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.

  4. If the PartitionGroupsOption property is set to False, set it to True.

  5. 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 How to: Define an Article (RMO Programming).

  6. Call the Create method.

  7. Repeat steps 5 and 6 for each article comprising the logical record.

  8. Create an instance of the MergeJoinFilter class to define the logical record relationship between articles. Then, set the following properties:

  9. 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.

  10. Repeat steps 8 and 9 for each remaining logical record relationship in the publication.

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 = "AdventureWorks";

// 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();
}

Community Additions

ADD
Show:
© 2014 Microsoft