Custom conflict resolution problem

Hi all,

I

am doing a work were i have to syncronize data between a Publiser

(PC, SQL SERVER 2005), and a subscriber (Pocket PC, SQL CE MOBILE). I

studied lots of articles and i managed to put the syncronization to

work, the problem is in conflict resolution!

The subscriber can′t insert or delet, only update!!

For exmple: I have a table that contais the quantity of a product X,

for example 100 units, this goes to Pocket, but later arrive move 100

units that are increased in the PC, so we get 200 in the PC, (but 100

in Pocket), no problem if i syncronize now (Pocket will have 200 too),

but if remove in Pocket for example 50 units, i've changed the same

column in both Publisher and Subscriber database, if i syncronize now,

i'll have a conflict, the final result should be 150 of product X in

both databases (100 + 100 - 50), but the Publisher wins the conflict

and the final result is 200!

I never worked with stored procedures and in microsoft theres and article called How to: Implement a Stored Procedure-Based Custom Conflict Resolver for a Merge Article (Replication Transact-SQL Programming), but they dont explain it very well and i dont know how to do my own stored procedure and implement it to the merge article.

in my own stored procedure i'll have to do some calculations to get the

result i want, maybe using some table for the additions and

subtractions. (Does the com based Addition resolver do this for me?)

Maybe you have experienced some problem like this, and could help me!

How should i do the stored procedure?

Oh, i tried to use de addiction resolver and the average resolver

of com based but nothing happed, dont know why, i read some stuff and

i think the addition could solve my problem, but it doesn′t work, i read above what you said and installed service

pack 1 for sql 2005, but i instaled and nothing

happened!
When i change to the resolver that the subscriver wins, it works, why addition dont work?

Thanx

JohnCP, forget about the conflict resolvers for now, the question I have is regarding how you're creating your conflict. Please explain to me again the series of steps you took, is it:

1. delete 50 rows from pocket pc

2. update column at publisher

3. update column at subscriber

Is this correct? I cannot understand why you still have 200 rows existing, can you further explain?

|||

I split your post, please refrain from posting your same question to multiple threads.

Regarding custom resolver, you most likely will have to create your own custom COM resolver to resolve this type of an issue.

|||Hi Greg, thx for the answers
I solve the problem with business logic handlers.

Here is the code, maybe it could help more ppl

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