merge replication, later wins conflict resolver issue

I am testing a scenario with merge replication when publisher has an entry
1st, and the same data entered into the subscriber later (but before merge
agent runs).
I am using merge replication with 'Microsoft SQL Server DATETIME (Later
Wins) Conflict Resolver'.
I inserted the same row (row with same PK) into publisher, then into
subscriber.
I am expecting to see row from subscriber replicate to publisher at the next
time merge agent runs.
Instead the row from publisher shows up in my subscriber side.
The conflict resolver reports that the publisher won:
The row was inserted at 'EZROUTESUNDB01.repltest' but could not be inserted
at 'EZROUTESTGDB01.repltest'. Violation of PRIMARY KEY constraint 'P_test1'.
Cannot insert duplicate key in object 'test1'.
The conflict table has conflict type 5, "Upload Insert Failed".
I want the latest row from subscriber to win that is why I choose "Later
Wins" resolver.
BTW, when I update the same row on both sides, repl. works like a charm,
later change will won independent of which side had it originally (so
subscriber data migrates to publisher OK if newer).
Also, when new inserts are made in either side, those are merged correctly.
Am I misunderstanding something here?
Thanks,
Zoltan
did you specify the time data type column to use as a basis for the later
wins resolver? You enter this is the Enter Information Needed by the
Resolver text box.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"rr news" <znyiri@.hotmail.com> wrote in message
news:HFXad.11969$yP2.4853@.tornado.tampabay.rr.com. ..
> I am testing a scenario with merge replication when publisher has an entry
> 1st, and the same data entered into the subscriber later (but before merge
> agent runs).
> I am using merge replication with 'Microsoft SQL Server DATETIME (Later
> Wins) Conflict Resolver'.
> I inserted the same row (row with same PK) into publisher, then into
> subscriber.
> I am expecting to see row from subscriber replicate to publisher at the
next
> time merge agent runs.
> Instead the row from publisher shows up in my subscriber side.
> The conflict resolver reports that the publisher won:
> The row was inserted at 'EZROUTESUNDB01.repltest' but could not be
inserted
> at 'EZROUTESTGDB01.repltest'. Violation of PRIMARY KEY constraint
'P_test1'.
> Cannot insert duplicate key in object 'test1'.
> The conflict table has conflict type 5, "Upload Insert Failed".
> I want the latest row from subscriber to win that is why I choose "Later
> Wins" resolver.
> BTW, when I update the same row on both sides, repl. works like a charm,
> later change will won independent of which side had it originally (so
> subscriber data migrates to publisher OK if newer).
> Also, when new inserts are made in either side, those are merged
correctly.
> Am I misunderstanding something here?
> Thanks,
> Zoltan
>
|||Hilary,
Thanks for you fast response.
Here are the table definition, the article info, and my test scenarios.
I am only having problem with scenario 3.
Thanks,
Zoltan
CREATE TABLE [test1] (
[num] [int] NOT NULL ,
[date] [datetime] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT
[DF__test1__rowguid__353DDB1D] DEFAULT (newid()),
[strcol] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [P_test1] PRIMARY KEY CLUSTERED
(
[num]
) ON [PRIMARY]
) ON [PRIMARY]
GO
sp_helpmergearticle @.article='test1'
id name
source_owner source_object
sync_object_owner sync_object
description status
creation_script conflict_table
article_resolver
subset_filterclause
pre_creation_command schema_option type column_tracking resolver_info
vertical_partition destination_owner
identity_support pub_identity_range identity_range threshold
verify_resolver_signature destination_object
allow_interactive_resolver fast_multicol_updateproc check_permissions
-- ---- --
---- --
--- --
-- ---
-- ---- --
-- ---- --
---- --
-- ---
-- -- -- -- --
-- ---- --
-- ---- --
-- -- -- -- --
-- ----
-- -- -- --
1 test1 dbo
test1 dbo
test1 NULL
2 NULL
conflict_repltest_test1 Microsoft SQL
Server DATETIME (Later Wins) Conflict Resolver NULL
1 0x000000000000CFF1 10 1 date
1 dbo
0 NULL NULL NULL 0
test1 0
1 0
I have a datetime type column named "date" which is entered in the
resolver_info field for article 'test1'.
article_resolver is configured for 'Microsoft SQL Server DATETIME (Later
Wins) Conflict Resolver'
Test scenarios:
1) new (row w/diff PK) inserted into either side, each row replicated
(merged to other side), OK
2) same row updated with different data, then the latest entry wins no
matter if it was created on the publisher side or the subscriber side
3) the only problem I have is when same row (row w/same PK) entered into
both sides.
EZROUTESTGDB01 publisher
EZROUTESUNDB01 sunscriber
--> 2nd scenario, later wins resolver works fine.
-- testing update same row, 1st at subscriber, then publisher
-- insert test row
insert into EZROUTESTGDB01.repltest.dbo.test1 (num, date, strcol) values(
102, getdate(), 'XXX' )
-- wait until it synchs, so both sides have same row
update EZROUTESTGDB01.repltest.dbo.test1 set date=getdate(), strcol='publ'
where num=102
-- wait few seconds
update EZROUTESUNDB01.repltest.dbo.test1 set date=getdate(), strcol='subscr'
where num=102
-- wait until synch, both sides end up with 'subscr' in strcol column, OK
--> 3rd scenario, NOT OK when subscriber has the later entry, publisher
still wins...?!
--clean up, wait until synch
delete from repltest.dbo.test1 where num > 20
-- testing insert same PK into publisher 1st, then subscriber, still
publisher wins, NOT OK!
insert into EZROUTESTGDB01.repltest.dbo.test1(num, date, strcol) values(
101, getdate(),'publisher' )
-- wait few seconds
insert into EZROUTESUNDB01.repltest.dbo.test1(num, date, strcol) values(
101, getdate(),'subscriber' )
-- wait until synch, both sides end up with 'publisher' in strcol column,
NOT OK
Resolver reports:
The row was inserted at 'EZROUTESUNDB01.repltest' but could not be inserted
at 'EZROUTESTGDB01.repltest'. Violation of PRIMARY KEY constraint 'P_test1'.
Cannot insert duplicate key in object 'test1'.
BTW, clocks are synched, I checked it.
Merge agent is configured to run once/minute.
Microsoft SQL Server 2000 - 8.00.760 on both sides.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:em7nkYMsEHA.2144@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> did you specify the time data type column to use as a basis for the later
> wins resolver? You enter this is the Enter Information Needed by the
> Resolver text box.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "rr news" <znyiri@.hotmail.com> wrote in message
> news:HFXad.11969$yP2.4853@.tornado.tampabay.rr.com. ..
entry[vbcol=seagreen]
merge
> next
> inserted
> 'P_test1'.
> correctly.
>