Merge replication deleting records because of primary key constraint violation

I am Running SQL Server 2000 with merge replication
I have a table that looks something like this...

OrderID - NVARCHAR(50)
LineNumber - BigInt - Identity +1 increment
<other columns>

I have both of these columns set up as the primary key, but for some reason replication is occasionally deleting these records because of a primary key constraint violation on the subscriber.  I understand why replication has to delete the records, but I just don't know why there would be an existing record on the subscriber.  If were happening every time, that would be one thing, but it's only happening about once out of every 1,000 records.  Can anyone offer some advice on how to track down why I am getting these violations?

These records are only being added by an application that only connects to the publisher, so I know that nobody is adding records manually to the subscriber.
Who is Participating?
ok scottieSLG,
Good that your problem is solved now close the question.

How many record in this table right now?

There are few things you need to check in this case.
1. Do you have any trigger(userdefined not replication trigger) on that table. if yes make it Not for replication.
2. Possibility that some one is adding record in subscriber.
3. Check for the conflict table at the publisher and look for the records deleted.
4. Check for the replication option and configure it to be publisher wins always.
5. Check if the applciation is inserting two records at the time (run profiler)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ScottieSLGAuthor Commented:
gad_fly, there are currently 161,000 records.

1. No, there are no additional triggers.
2. I've looked at the scenarios from some of the items that have been deleted and I am certain that they were not added by anyone on the subscriber.
3. The conflict table is what brought me to realize this was happening.  
4. I'm not sure I want to do this, because it is possible that in a different scenario the subscriber would win the conflict.
5. I will try this.

A question I had, does replication necessarily do actions in the order that they are done on the publisher?  My theory is that the application is adding the records on the publisher, then a user runs a program that deletes and re-adds the records.  So, on the subscriber, it should add/delete/add.  But, could it possibly perform add/add/delete instead?
Would upgrading to SQL Server 2005 help at all?
<<does replication necessarily do actions in the order that they are done on the publisher? >>
Yes for One Particular Publication.

but if you have more than one publication on same table. then you can violate this rule.
ScottieSLGAuthor Commented:
I found the problem...

For some reason, I had the 2 columns set as the primary key on the publisher, but the subscriber only had the second column set as the primary key.  I added the primary key to the first column as well and set the autogenerated column as "not for replication".  I'm pretty sure this will fix the problem.

Thanks for all your help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.