Example of Stored proc conflict resolver?

Hi all,

I need to write a custom conflict resolver for an application using merge replication. It's relatively simple logic - compare the two rows from the publisher and the subscriber and the winner is based on the value of one particular column.

Reading BOL gives me the input parameter list for the sp, and specifies that the output should be exactly the winning row. What is doesn't give is any example of how to do this, in particular how to access the two rows in conflict so that they can be compared.

I can do this by dynamically building SELECT statements, one connecting to the table locally on the publisher and another connecting to the subscriber using the form SERVER.DATABASE.OWNER.TABLE, but this requires me to explicitly have the subscriber as a linked server to do this. An example of my revolting code is appended to this post.

Is this what I have to do, or is there some table I can access on the publisher that has the conflicting rows in so I can compare them without going back to the subscriber?

Thanks for your help

Richard

- code sample --

ALTER PROCEDURE [dbo].[prRep_ResolveInventoryConflicts]

@.tableowner sysname, @.tablename sysname, @.rowguid uniqueidentifier,
@.subscriber sysname, @.subscriber_db sysname,
@.log_conflict int OUTPUT, @.conflict_message nvarchar(512) OUTPUT,
@.destination_owner sysname

AS

BEGIN

DECLARE @.qrySubscriber varchar(255)
DECLARE @.qryPublisher varchar(255)
DECLARE @.Publisher sysname
DECLARE @.RecentCheck datetime

-- Temp table with same form as tbl_Inventory

CREATE TABLE #ConflictingRows (
[Machine] [sysname]NOT NULL,
[LocationID] [int] NOT NULL,
[PartGUID] [uniqueidentifier] NOT NULL,
[Qty] [int] NOT NULL ,
[LastUpdatedDtm] [datetime] NOT NULL,
[LastUpdatedUser] [varchar](50) NOT NULL ,
[rowguid] [uniqueidentifier] NOT NULL

)

-- Build the T-SQL To run against publisher and subscriber

SET @.Publisher = @.@.Servername
SET @.qryPublisher =
'INSERT INTO #ConflictingRows
SELECT '+@.Publisher+' as Machine, * FROM '+@.tableowner+'.'+@.tablename+
'WHERE rowguid = '+CAST(@.rowguid as varchar(40)) +';'

SET @.qrySubscriber =
'INSERT INTO #ConflictingRows
SELECT '+@.subscriber+ ' AS Machine, * FROM '+@.subscriber+'.'+@.subscriber_db+'.'+@.tableowner+'.'+@.tablename+
'WHERE rowguid = ' + CAST(@.rowguid as varchar(40)) +';'

-- execute the stored procedures

EXECUTE @.qryPublisher;
EXECUTE @.qrySubscriber;

-- Compare the two rows and return the winning row, ie the one that was last updated by a human checking the inventory

SELECT @.RecentCheck = MAX(LastUpdatedDtm) FROM #ConflictingRows

SELECT TOP 1 * FROM #ConflictingRows where LastUpdatedDtm=@.RecentCheck;

-- Cleanup and exit

DROP TABLE #ConflictingRows

END

-

Hi,

Obviously no-one else is trying to to T-SQL based resolvers!

This post doesn't provide the answer to the generic question of how to access both rows, but I found the solution to my specific problem. MS provide a set of built-in COM-based resolvers, one of which does exactly what I want, and selects the winning row based on the latest value in a specifed DateTime column.

A full list of the COM Based resolvers is on SQL 2005 BOL at:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/a6637e4b-4e6b-40aa-bee6-39d98cc507c8.htm

Hope this helps someone,

Rich

|||Hi there Richard, i know you find the answer to your problem in the list of the COM Based resolvers, but did you discover how to create your resolver?
If yes, i hope you can help me!

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 so me stuff and i think the addition could solve my problem, but it doesn′t work, in another foruns people were having problems too and installed service pack 1 for sql 2005 and then it worked, but i instaled and nothing happened!

Thanx

|||

Hi John,

This is probably worth spooling out as a seperate question, and seeing how others have solved it. If you've never written SPs and you're getting involved in the depths of merge replication then I wish you the very best of luck!

My solution would be along these lines:

Include a change to the design of the database so you have an audit log tracking the movements in and out of a stock location. Imagine you start with a +100 entry into this log. Then when the PC adds +100 and the Pocket adds -50 the log synchronises and the sum total in both logs is +150. Your quantity is then the sum of that log, rather than a value in a particular table. If your application requires it then you use a view to give you the equivalent of the original inventory table..

This does create an additional issue, in that over time queries will get slower if there are huge numbers of entries in this log, as the view has to do the query to . But you can solve that by periodically purging the log and starting again with the defined number.

Hope this helps.

Richard

|||HI Richard, thx for answer
I did not used a log, but 2 new columns an it works fine, but i had to create the update handler too, to clean these news coluns every time i update, because coud exist forward errors in calculations.

Here is the code, it could help someone in the future:

//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);
}
}|||I'm happy Richard was able to use the DateTime resolver, but unfortunately I'm still stuck needing to use the Stored Procedure resolver and wrestle with his original problem: how to access the subscriber record and compare it to the publisher without have to have a live connection to the subscriber. Does anyone know? I know that SQL Server stores the losing record, but I assume it would not be there since the winner has yet to be established.|||Nevermind. I've decided to try a business logic handler for the conflicts.|||

Hi.

Actually it now turns out that I can't use the max date time updated in all the locations I intended to, so I'm back to square one as well!

What's the business logic handler type, and how do you find it from SSMS?

Richard

|||A Business Logic Handler can be used to implement just about any kind of business logic during replication. That is to say it is not just a conflict resolver, but it can definately be used as one. It is not part of SSMS, per se, it is .Net code (a class within a DLL) that is registered within SQL Server so that it appears in the conflict resolver list, just as the Stored Procedure resolver is. You can find out how to implement one here: http://msdn2.microsoft.com/en-us/library/ms147853.aspx and here http://msdn2.microsoft.com/en-us/library/ms147911.aspx. Make sure that when you specify the @.dotnet_class_name parameter, that you use the fully-qualified name (that is, Namespace.Classname).|||

Thanks for that. I think I've run into another unforeseen problem as well. I blame the idiot who designed the db schema ;-)

Richard