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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


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)
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?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

<<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.
ok scottieSLG,
Good that your problem is solved now close the question.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.