February 15, 2016 at 7:42 am
We use SQL server 2012 merge replication to replicate data between 11 databases. At some time a conflict type 5 & type 6 (upload & download conflict) occured between 2 rows in the same table. In 2 servers a row was created having the same value in a table on which a unique constraint exists. Normally these columns should never receive the same value in 2 hubs, but this time they did. My task: tell the good people in my company from which hub the data came. The first was easy of cause: the hub that couldn't upload it's copy into the publisher database was one of the partners. But from which of the other 10 hubs did the conflicting copy originate that was already in the publisher? All I've got is the rowguid value for the row and the article the replicated table is in and the row was created recently so it is still within the retention period for my merge replication.
Here's the query I ran against all hubs to find the one that the row originated from. As there is very little documentation on the internals of the merge engine I would very much like your comments on my solution to see if I've not overlooked some easier obvious solution or maybe even did make some crucial error.
declare @rowguid uniqueidentifier;
select @rowguid = 'E0EC1043-ED04-4C9C-913B-622A14D4193A';
select top 1000
sma.name,
gh.*
from dbo.MSmerge_genhistory gh with (nolock)
inner join dbo.sysmergearticles sma with (nolock) on (sma.nickname = gh.art_nick)
where gh.genstatus = 1
and gh.generation in (
select mc.generation
from [dbo].[MSmerge_contents] mc with (nolock)
where mc.tablenick = sma.nickname
and mc.rowguid = @rowguid
)
and gh.art_nick = (
select sma.nickname
from dbo.sysmergearticles sma with (nolock)
where sma.objid = object_id('dbo.MyReplicatedTable')
)
order by gh.generation, gh.coldate;
I ran this query against all hubs, looking for all dbo.MSMerge_genhistory.genstatus of 1 - Closed. Having at least one row for my table and row in MSmerge_contents. Exactly one row is returned on exactly one hub. This must be the hub in which the row originally created. All other hubs return no rows. Any comments please?
February 15, 2016 at 8:05 am
The column "origin_datasource_id" in the conflict table contains the "subid" column in the MSmerge_subscriptions in the distribution database.
Query the MSmerge_subscriptions table using that origin_datasource_id as a filter for subid and you will immediately identify where the row comes from.
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply