Merge replication - examples for custom conflict resolvers?

I have gone through BOL and various online resources including this one and I can't find any examples on how to use custom conflict resolvers to generate a result row which is a combination of the rows to be merged.

BOL says it can be done, but suitable examples are missing. I have found various posts requesting examples from up to a year ago, but can see no replies with relevant information

In particular I would like to see examples of

1) A stored procedure based custom conflict resolver

2) A business logic handler written in VB .Net

Here's hoping

aero1

Answer this question

Merge replication - examples for custom conflict resolvers?

Chhaya
There were no synch errors at all

I checked the sqlmergx.txt file on the SQL Express client and found no errors

No errors in the SQL Server error log or the event logs on either publisher or subscriber

================

I set up a new subscriber database on the same box as the publisher and got the same problem

================

I then decided to try an even simpler conflict resolver which comprised the following - ie accept the publisher data

customDataSet = publisherDataSet.Copy

Return ActionOnUpdateConflict.AcceptCustomConflictData

Again - this is setting the correct data on the publisher - but the correct data does not appear in the subscriber

I am starting to tear my hair out on this - so any suggestions would be much appreciated

Thanks aero1
Kanhaiya
Thanks JohnCP - that's given me a good start.

I have a couple of follow on questions.

1) I am using column level tracking and in the resolver I need to know which column(s) have given a conflict for the row. How can I find this information

2) I need to know which columns have changed on the subscriber since the last synch. Can I find this information directly within the resolver

I can get this information by using sp_showreplicainfo on the subscriber, but this means a database access within the resolver. Is this OK or is there a better way (I presume that the dataset current values are equal to the original at this point - ie the subscriberDataSet original version isn't what the subscriber obtained from the publisher at the last merge)

Note that what I am ultimately trying to achieve is that for columns in conflict, the column values in the row with the latest update should win (using a dateLastUpdated column). Other columns which may have changed should be merged as normal - i.e. it is not acceptable to replace the whole row according to the dateLastUpdated column.

Thanks again - aero1
Keyth
1) The conflict table shows the losing row - and even the conflict manager interface in Management Studio only shows the publisher and subscriber rows. You can't tell which columns are causing conflict. Just because they are different doesn't necessarily mean that there is a conflict

2) Sorry for any ambiguity - I need the columns changed on the subscriber since the last synch - but only when there is a conflict

The built-in resolver you suggest doesn't do the job. - It will replace the whole row by either the subscriber or the publisher row. I need to combine subscriber and publisher rows in the event of conflict.

What I am currently considering is to create a backup database at the subscriber (of the subscriber database) after a synchronisation. I can then access the 'original' rows from the backup database to identify changes. As the subscriber databases are heavily filtered the resources needed to maintain the backup database should not be excessive.

Any better ideas would be much appreciated

Thanks - aero1
SoulSolutions
Just in case the problem was due to some complexity in the table definition, I repeated the test with an even simpler table - details below

Merge synch gave exactly the same error - the correct data is set on the publisher, but the correct data does not appear on the subscriber. All I am doing is updating the middlename to different values on publisher and subscriber and then synchronizing

I also noticed that the merge agent dialog box just reports that 1 update and 0 conflicts have occcurred. Although the conflict has been resolved, I would have expected a conflict to have been reported

CREATE TABLE dbo.simpletable(

[ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[NameStyle] bit NOT NULL DEFAULT ((0)),

[Title] [nvarchar](10) COLLATE Latin1_General_CI_AS NULL,

[FirstName] NVARCHAR(50) NOT NULL,

[MiddleName] NVARCHAR(50) NULL,

[LastName] NVARCHAR(50) NOT NULL

CONSTRAINT [PK_simpletable] PRIMARY KEY CLUSTERED

(

[ContactID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT INTO [testrepl].[dbo].[simpletable]

([NameStyle]

,[Title]

,[FirstName]

,[MiddleName]

,[LastName])

select

[NameStyle]

,[Title]

,[FirstName]

,[MiddleName]

,[LastName]

from person.contact
sanwanas
I have got a simple conflict handler installed now. For the moment, I am just doing a test by updating the middlename column in a copy of the person.contact table from adventureworks. The conflict handler is being called, and the required row is being generated on the publisher - but the subscriber is not being set correctly.

The conflict handler should concatenate the middlename from the publisher - with a literal 'T' and then concatenate with the middlename from the subscriber. I did this just to check that I could get the publisher and subscriber values OK

When I perform conflicting updates such as

update person.contact set middlename='B' where contactid=1 (on the publisher)

update person.contact set middlename='C' where contactid=1 (on the subscriber)

On synchronisation - middlename = 'BTC' on the publisher which is correct, but the value on the subscriber is 'C' - which is incorrect

Any ideas what is going wrong - The conflict handler is listed below. (Note that my publisher is SQL developer SP1 and my subscriber is SQL Express SP1)

The problem occurs with both column level tracking and row level tracking

Thanks - aero1

Public Overrides Function UpdateConflictsHandler(ByVal publisherDataSet As System.Data.DataSet, ByVal subscriberDataSet As System.Data.DataSet, ByRef customDataSet As System.Data.DataSet, ByRef conflictLogType As Microsoft.SqlServer.Replication.BusinessLogicSupport.ConflictLogType, ByRef customConflictMessage As String, ByRef historyLogLevel As Integer, ByRef historyLogMessage As String) As Microsoft.SqlServer.Replication.BusinessLogicSupport.ActionOnUpdateConflict

customDataSet = publisherDataSet.Copy

customDataSet.Tables(0).Rows(0).Item("MiddleName") = _

customDataSet.Tables(0).Rows(0).Item("MiddleName") & "T" & _

subscriberDataSet.Tables(0).Rows(0).Item("MiddleName")

Return ActionOnUpdateConflict.AcceptCustomConflictData

End Function
Jens Sauer
You might want to look at the additive conflict resolver. This will do something like concatenate or add the two values together.

You can also use a stored procedure resolver to incorporate your own loig.
Bagload
Hi aero1.
I made one business handler, but its in C#, maybe you could see and convert to VB.
In this case, i wanted to merge AddToPub and SubToPub colums, make some calculations and put it in colum Quantity.
Hope it helps

Here is the code

public class Class1 : Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule
{
// Variables to hold server names.
private string publisherName;
private string subscriberName;

// Implement the Initialize method to get publication
// and subscription information.
public override void Initialize(string publisher, string subscriber, string distributor,
string publisherDB, string subscriberDB, string articleName)
{
// Set the Publisher and Subscriber names.
publisherName = publisher;
subscriberName = subscriber;
}

// Declare what types of row changes, conflicts, or errors to handle.
override public ChangeStates HandledChangeStates
{
get
{
// Handle Subscriber inserts, updates and deletes.
return ChangeStates.UpdateConflicts |
ChangeStates.SubscriberUpdates | ChangeStates.PublisherUpdates;
}
}

//Treats update conflict
public override ActionOnUpdateConflict UpdateConflictsHandler(
DataSet publisherDataSet,
DataSet subscriberDataSet,
ref DataSet customDataSet,
ref ConflictLogType conflictLogType,
ref string customConflictMessage,
ref int historyLogLevel,
ref string historyLogMessage
)
{
//copies publisher dataset to customdataset
customDataSet = publisherDataSet.Copy();

//Quantity of the Publisher
int qPub = Int32.Parse(publisherDataSet.Tables[0].Rows[0]["Quantity "].ToString());

//Quantity of the Subscriber to Add to Publisher
int qSubA = Int32.Parse(subscriberDataSet.Tables[0].Rows[0]["AddToPub"].ToString());
//Quantity of the Subscriber to subtract to Publisher
int qSubS = Int32.Parse(subscriberDataSet.Tables[0].Rows[0]["SubToPub"].ToString());

int qFinal = qPub + (qSubA - qSubS);
//Insert final values in customDataSet that will be the data in both Pub and Subscriber
customDataSet.Tables[0].Rows[0]["Quantity "] = qFinal;
customDataSet.Tables[0].Rows[0]["AddToPub"] = 0;
customDataSet.Tables[0].Rows[0]["SubToPub"] = 0;

return ActionOnUpdateConflict.AcceptCustomConflictData;
}

//Treats normal update without conflict
public override ActionOnDataChange UpdateHandler(SourceIdentifier updateSource,
DataSet updatedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
ref string historyLogMessage)
{
//if it's subscriber doing update -clean columns AddToPub, SubToPub
//to avoid errors
if (updateSource == SourceIdentifier.SourceIsSubscriber)
{
//copies dataset thats being updated to customdataset, where i'll make changes and return it
customDataSet = updatedDataSet.Copy();
//Insert final values in customDataSet that will be the data in both Pub and Subscriber
customDataSet.Tables[0].Rows[0]["AadicionarAPub"] = 0;
customDataSet.Tables[0].Rows[0]["AsubtrairAPub"] = 0;
// Accept the updated data in the Subscriber's data set and apply it to the Publisher.
return ActionOnDataChange.AcceptCustomData;
}
else
{
return base.UpdateHandler(updateSource, updatedDataSet,
ref customDataSet, ref historyLogLevel, ref historyLogMessage);
}
}

NastyMatt

1 - You can see the conflicts in the publication - in Servre Managent right click and you have: view conflict, there you have the conflict table, colum that conflict, conflict winner and loser, and you can submit the winner or the loser, if the default conflit resolver didn't choose one
2 - I'm not sure, Wihtout conflit You can make a resolver, to Handle Updates without conflit, and log the changes, see this microsoft example:

Public Overrides Function UpdateHandler(ByVal updateSource As SourceIdentifier, _
    ByVal updatedDataSet As DataSet, ByRef customDataSet As DataSet, _
    ByRef historyLogLevel As Integer, ByRef historyLogMessage As String) _
    As ActionOnDataChange

      If updateSource = SourceIdentifier.SourceIsPublisher Then
        ' Build a line item in the audit message to log the Subscriber update.
        Dim AuditMessage As StringBuilder = New StringBuilder()
        AuditMessage.Append(String.Format("An existing order was updated at {0}. " + _
         "The SalesOrderID for the order is ", subscriberName))
        AuditMessage.Append(updatedDataSet.Tables(0).Rows(0)("SalesOrderID").ToString())
        AuditMessage.Append("The order must now be shipped by :")
        AuditMessage.Append(updatedDataSet.Tables(0).Rows(0)("DueDate").ToString())

        ' Set the reference parameter to write the line to the log file.
        historyLogMessage = AuditMessage.ToString()
        ' Set the history log level to the default verbose level.
        historyLogLevel = 1

        ' Accept the updated data in the Subscriber's data set and apply it to the Publisher.
        Return ActionOnDataChange.AcceptData
      Else
        Return MyBase.UpdateHandler(updateSource, updatedDataSet, _
         customDataSet, historyLogLevel, historyLogMessage)
      End If
    End Function

But you want source is Subscriber, change in the function, and append the columns you want.

But i think you dont need to make any resolver, cause you want the latest update to win, so you have a Costum resolver already for use that is Microsoft Sql Server DATETIME (Later Wins) Conflcit resolver, or the earlier wins

sd_dracula
In the conflictUpdateHandler you have both publisher dataset and subscriber dataset, the resolver runs for every conflict, and dont do anything when theres no conflict!
If i understand when happens a conflict you want, check who( publisher or subscriber) altered first the conflict column.
Dont know if you have any column to insert the date.
You could do something like;
When you change anything on either( publisher or subscriber) you put the data in column date, then when publisher and subscriber alter same column, you will have the conflict, and there enters the conflict handler and the column date.

1o.
//copy the publisher DB to customDataSet
customDataSet = publisherDataSet.Copy();

2o.
//Check who inserted first
//And change only the column in conflict, the other dont do anything
datetime pubDate = publisherDataSet.Tables[0].Rows[0]["DateColumn"].ToString());
datetime subDate = subscriberDataSet.Tables[0].Rows[0]["DateColumn"].ToString());
Maybe you have to make some casts here

if( PubDate > subDate ) //your customDataSet will be changed in the way you want
{
customDataSet.Tables[0].Rows[0]["ConflictColumn"] = something from the one you want to win (in this case from Publisher thats the recent one);
}else
customDataSet.Tables[0].Rows[0]["ConflictColumn"] = something from the one you want to win (in this case from Subscriber thats the recent one);

3o
return ActionOnUpdateConflict.AcceptCustomConflictData;
Alastair Q
Thanks for your reply,

Actually what am trying to do is to replicate two separate SharePoint 2007 farm. The replication is working fine except for the document library.

When multiple documents are uploaded at the same time and before the synchronization happening, the conflict will happen. This is because the documents in different sites are having same primary keys and primary keys cannot be replicated.

So, what am trying to do is to use conflict resolver so that it will merge the documents, what I mean by merge is to have both rows from both sites replicated.

Am trying to create sp in order to do this,

If you have any suggestion, please advise,

Regards,
Philip Painter
Hi there, sorry but i have no idea what the problem is, i did some research and didn’t find anything relevant. I was having some problems with SQL Express, then changed to SQL Server 2005 Trial version with SP1, but i dont know if it would solve your problem too.
J. Brian
Thanks - I will mark the original question as answered - as you got me started with an example.

I have raised a separate thread to cover the more specific problem - so consider this one closed

Thanks for your help

aero1
Andy Jarvis
Hi,

"Insert conflicts. Insert conflicts occur when a row is inserted at one location that violates some data consistency rule when merged with changes at other locations. For example, if two rows are inserted at two locations, and each row uses the same primary key, the two changes will conflict when the changes are merged. One insert wins, and the other one loses. You have the option to keep the existing data (the data that won), overwrite the existing data with the data that conflicted with it (the losing data), or merge the winning and losing data and update the existing data."

http://technet.microsoft.com/en-us/library/ms189029.aspx

I found this article on the above link on the microsoft web site,

What I want is to merge the winning and losing data and update the existing data,

Is this possible
Eby
Thats strange, after the Sync, both Publisher and Subscriber must have BTC. I don't know why customData don't go to the subscriber too.
You didn't get any sync error
Merge replication - examples for custom conflict resolvers?

Answer this question