Post reply

Receving a Primary Key Violation in my Target Table that has the exact same layout as the Source Table and the same primary key

  • August 7, 2017 at 7:38 am

    #338672

    I've got a very weird issue going on with a SSIS Truncate and Load Package. The database that I'm running this SSIS Package on is a Subscriber to a Publisher(i.e. Transnational Replication). Every once in awhile I'll get a
    Primary key violation(duplicate key) when I know that the source table has a the exact same primary key that the target table has and I have verified via sql that there are no duplicates on the primary key in the source table.
    Now  I'm using a select statement with a nolock to retrieve the data from the source to populate into the target. I'm thinking that this could be something to do with my Transnational Replication and the fact that I'm doing "dirty reads"
    and  should  change the code to not us a nolock hint.

    You  reply's are greatly appreciated.

  • August 7, 2017 at 7:42 am

    #1954304

    Try without the nolock, see if you can reproduce it.

    Nolock allows duplicate reads, in addition to dirty reads  (and missed rows as well), so that's a probably cause for your problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 7, 2017 at 8:16 am

    #1954313

    Thanks! I think so too

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply