Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Isn't the database suppose to be specified in a custom Business logic Merge replication resolver

Answered Isn't the database suppose to be specified in a custom Business logic Merge replication resolver

  • Friday, November 30, 2012 5:58 PM
     
     
    Sign In to Vote
    0

    I think the answers are obvious, but I have to ask some questions. First, how is the database specified in this example? Is it done when you define the article, if so how.

    http://code.msdn.microsoft.com/Merge-Business-Logic-58d21795

    How would you get the value of a specific database field.

    How would you modify a loser integer field and winner integer field to increase both by 1.

    How would you concatentate text data (keep text belonging to both) between to servers.

    This is all I need to begin programming.

    What else will I need to do in the code.

    Thanks,

     

All Replies

  • Friday, November 30, 2012 6:10 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    First you develop the custom conflict resolver, deploy and register it, then specify to use the resolver when you define an article.

    Have a look at Implement a Business Logic Handler for a Merge Article which provides every step needed to develop, deploy, register, and use a business logic handler.

    Brandon Williams (blog | linkedin)

     
  • Friday, November 30, 2012 6:46 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    I suggest looking at CommitHandler, DeleteErrorHandler, DeleteHandler, InsertErrorHandler, InsertHandler, UpdateConflictsHandler, UpdateDeleteConflictHandler, UpdateErrorHandler, and UpdateHandler to see the parameters that get passed to each handler.  In most cases you will have 1 or 2 datasets to work with.

    For example, the UpdateConflictsHandler comes with publisherDataSet and subscriberDataSet which are datasets representing the conflicting publisher and subscriber data respectively.

    Brandon Williams (blog | linkedin)

     
  • Friday, November 30, 2012 7:28 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    To concatenate text data the body of a handler would look something like this:

    // Get publisher and subscriber text data field
string publisherTextDataField = publisherDataSet.Tables[0].Rows[0]["TextField"].ToString();
string subscriberTextDataField = subscriberDataSet.Tables[0].Rows[0]["TextField"].ToString();

// Concatentate text data
string concatTextDataField = publisherTextDataField + subscriberTextDataField;

// Set custom business logic dataset
customDataSet = PublisherDataSet.Copy();
customDataSet.Tables[0].Rows[0]["TextField"] = concatTextDataField;

// Save custom dataset
return ActionOnUpdateConflict.AcceptCustomConflictData;

    Brandon Williams (blog | linkedin)

     
  • Friday, November 30, 2012 7:30 PM
    Moderator
     
     
    Sign In to Vote
    0
    I am not sure about your question about needing to increment both losing and winning integer fields by 1 on a conflict.  Doing so would likely end up in non-convergence so it doesn't make very much sense to do.

    Brandon Williams (blog | linkedin)

     
  • Saturday, December 01, 2012 9:50 AM
     
     
    Sign In to Vote
    0
    I am not sure about your question about needing to increment both losing and winning integer fields by 1 on a conflict. 

    Sorry, I stated the question incorrectly. If I have a conflict because 2 servers increased a field by 1 (the value is now 120 for both) how do I put 121 in the field for both servers. They should have 121 and not 120. What methods should I use to do this. What would the code look like.

    Thanks,


     
  • Saturday, December 01, 2012 6:50 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    It would look something like this:

    public override ActionOnUpdateConflict UpdateConflictsHandler(DataSet publisherDataSet, DataSet subscriberDataSet, ref DataSet customDataSet,
        ref ConflictLogType conflictLogType, ref string customConflictMessage, ref int historyLogLevel, ref string historyLogMessage)
{
    // Get publisher and subscriber integer field
    int publisherIntegerField = (int)publisherDataSet.Tables[0].Rows[0]["IntegerField"];
    int subscriberIntegerField = (int)subscriberDataSet.Tables[0].Rows[0]["IntegerField"];

    if (publisherIntegerField == subscriberIntegerField)
    {
        // increment the integer field by 1
        publisherIntegerField++;

        // Set custom business logic dataset
        customDataSet = publisherDataSet.Copy();
        customDataSet.Tables[0].Rows[0]["IntegerField"] = publisherIntegerField;
    }

    return ActionOnUpdateConflict.AcceptCustomConflictData;            
}

    Brandon Williams (blog | linkedin)

     
  • Saturday, December 01, 2012 7:21 PM
    Moderator
     
     
    Sign In to Vote
    0

    The problem with business logic resolvers is we don't know if a particular column has changed so that above solution(s) would probably not be suitable as the code will execute when any of the columns (column-level tracking) or rows (row-level tracking) are involved in an update-update conflict.

    This is where a COM-based is probably better suited as it provides column-level version information.

    I will start working on a COM-based example now.

    Brandon Williams (blog | linkedin)

     
  • Saturday, December 01, 2012 11:52 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    Okay, I am convinced you will need to utilize a COM-based resolver to accurately accomplish your goal(s) as you require to know if particular columns have been updated and resolve accordingly.  A COM-based resolver will offer column-level version information, a business logic resolver will not.

    Here is an example COM-based resolver to detect if your integer field is involved in an update-update conflict, checks if they have been updated to the same value on both source and destination, and if so, increments the value by 1 and saves the change.

    using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Text;
using SQLResolver_import;
using System.Runtime.InteropServices;

namespace COMConflictResolver_CSharp
{
    [Guid(COMCustomResolver.ClassId)]
    public class COMCustomResolver : ICustomResolver
    {
        private const int MAX_BUFFER_SIZE = 1048576;
        private const int MAX_NAME_LENGTH = 128;

        public const string ClassId = "825818F7-3531-4524-8B07-72343EFDC8AB";
        public const string InterfaceId = "CECFBB8F-584F-4733-9373-B69AFA6F117F";
        public const string EventsId = "5D55BA40-A438-4FD4-BA8B-05095DC89948";

        public COMCustomResolver()
            : base()
        {
        }

        public void GetHandledStates(ref uint ResolverBM)
        {
            ResolverBM = (uint)SQLResolver_import.REPOLE_CHANGE_TYPE.REPOLEUpdateConflicts;
        }

        public void Reconcile(IReplRowChange pRowChange, int dwFlags, IReplRowChange pvReserved)
        {
            uint cntColumns = 0;
            StringBuilder strColumnName = null;
            string columnName = null;

            bool blnIntegerIncremented = false;
            SQLResolver_import.REPOLE_COLSTATUS_TYPE ColStatus = default(SQLResolver_import.REPOLE_COLSTATUS_TYPE);
            uint intBufferLenActual = 0;
            int destinationIntegerColumnValue;
            int sourceIntegerColumnValue;
            int finalIntegerColumnValue;
            IntPtr myBuffer = Marshal.AllocHGlobal(MAX_BUFFER_SIZE);
            string strMsg = null;

            pRowChange.GetNumColumns(out cntColumns);

            for (uint i = 1; i <= cntColumns; i++)
            {
                //Get the column status of each column
                pRowChange.GetColumnStatus(i, out ColStatus);

                // If the column has been updated at both the Publisher and Subscriber
                if ((ColStatus == SQLResolver_import.REPOLE_COLSTATUS_TYPE.REPOLEColumn_UpdatedWithConflict))
                {
                    columnName = " ".PadRight(MAX_NAME_LENGTH);
                    pRowChange.GetColumnName(i, strColumnName, MAX_NAME_LENGTH);
                    columnName = strColumnName.ToString().TrimEnd();

                    // is this our integer column?
                    if ((string.Compare(columnName, "MyIntegerColumn", true) == 0))
                    {
                        // get the column values
                        pRowChange.GetDestinationColumnValue2(i, myBuffer, MAX_BUFFER_SIZE, out intBufferLenActual);
                        destinationIntegerColumnValue = myBuffer.ToInt32();
                        pRowChange.GetSourceColumnValue2(i, myBuffer, MAX_BUFFER_SIZE, out intBufferLenActual);
                        sourceIntegerColumnValue = myBuffer.ToInt32();

                        // verify the integer column was updated to the same value, ie. source = 120 and destination = 120
                        if (destinationIntegerColumnValue == sourceIntegerColumnValue)
                        {
                            // increment integer column by 1 and set the resolved data
                            finalIntegerColumnValue = sourceIntegerColumnValue + 1;
                            myBuffer = (IntPtr)finalIntegerColumnValue;
                            pRowChange.SetColumn2(i, myBuffer, MAX_BUFFER_SIZE);
                            blnIntegerIncremented = true;
                        }
                    }
                }
                else if ((ColStatus == SQLResolver_import.REPOLE_COLSTATUS_TYPE.REPOLEColumn_UpdatedNoConflict))
                {
                    // For columns that have not been updated - do nothing.
                    pRowChange.CopyColumnFromSource(i);
                }
                else if ((ColStatus == SQLResolver_import.REPOLE_COLSTATUS_TYPE.REPOLEColumn_NotUpdated))
                {
                }
            }

            // Log conflict and call the UpdateRow method to commit all the column value changes.
            if (blnIntegerIncremented)
            {
                strMsg = "Integer column - update-update conflict - column value incremented by 1";
            }
            else
            {
                strMsg = "Integer column - no conflict - column value not incremented by 1";
            }

            pRowChange.LogConflict(1, REPOLE_CONFLICT_TYPE.REPOLEConflict_ColumnUpdateConflict, 0, strMsg, 0);
            pRowChange.UpdateRow();
            Marshal.FreeHGlobal(myBuffer);
        }

        public void RemoteReconcile(SQLResolver_import.IReplRowChange pRowChange, uint dwFlags, SQLResolver_import.IReplRowChange pvReserved)
        {
        }
    }
}

    The solution is not complete nor has it been tested or debugged.  Let me know if you need any help if you end up going this route.

    Brandon Williams (blog | linkedin)