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

----