Merge replication Primary Key violations

I have a Merge Replication Problem and I'm not sure how to proceed.

Given the following:

Multiple clients have merge subscriptions to a central Server's DB.
DB contains a table tblUser
tblUser has a Varchar field; UserLoginName, which is Primary Key.
It also has an integer field, UserValue

The replication is configured to resolve to the greater integer value.

Client 1 creates a new User
INSERT tblUser
(UserLoginName, UserValue)
VALUES('Fred',1)
At the same time Client 2 does the same thing, but with a larger value
INSERT tblUSer
(UserLoginName, UserValue)
VALUES('Fred', 2)

One of the inserts will get to the Central Server first.
The other will form a conflict.

A row insert at 'Client2.dbUsers' could not be propagated to 'CentralServer.dbUsers'.
This failure can be caused by a constraint violation.
Violation of PRIMARY KEY constraint 'PK_tblUser_1'. Cannot insert duplicate key in object 'dbo.tblUser'.

So now I have two different value fields and a conflict that cannot be resolved.

Is this fixable?
Do I need to make changes at athe Architechture level?

Hi Brian,

if you need to store both records from all subscribers, You should add a column for client id .So once tables replicates from all subscribers each client has their own id.

Example: if client 1 has the code 100 and client 2 has the code 200

the table at subscriber will be : 100,'Fred',1 (Record comes from client 1)

200,'Fred',1 (Record comes from client 2)

if you need to resolve the conflict and to store 1 record only you should assign priority for each subscriber

For more details check the following URL : http://support.microsoft.com/kb/315514

Regards,

Tarek Ghazali

SQL Server MVP

http://www.sqlmvp.com

|||

I need to store only the higher of the two.

If the record already exists and both try to update the record, only the higher is stored, as desired.

But when the record does not exist, both subscribers do an insert, and it does not store only the higher.

Reading all the KB articles on merge replication, it appears that Updates and Inserts are supposed to be resolved the same.

I can't use subscriber priority, as I don't care WHERE the record came from, only the value stored.