Merge replication deleting records because of primary key constraint violation

Posted on 2006-04-10
Last Modified: 2012-08-14
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.
Question by:ScottieSLG
    LVL 2

    Expert Comment


    How many record in this table right now?

    LVL 28

    Expert Comment

    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)

    Author Comment

    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?
    LVL 28

    Expert Comment

    <<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.

    Author Comment

    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.
    LVL 28

    Accepted Solution

    ok scottieSLG,
    Good that your problem is solved now close the question.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now