Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Posts
    5

    Unanswered: Primary Key Violation Error in SQL 2005

    Hey guys...

    I've recently migrated a SQL 2000 db to SQL 2005. There is a table with a defined primary key. In 2000 when I try importing a duplicate record my application would continue and just skip the duplicates. In 2005 I get an error message "Cannot insert duplicate key row in object ... with unique index..." Is there a setting that I can enable/disable to ignore and continue processing when these errors are encountered? I've read a little on "fail package on step failure" but not quite clear on it. Any tips? Thanks alot

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by kimykimy
    Hey guys...

    I've recently migrated a SQL 2000 db to SQL 2005. There is a table with a defined primary key. In 2000 when I try importing a duplicate record my application would continue and just skip the duplicates. In 2005 I get an error message "Cannot insert duplicate key row in object ... with unique index..." Is there a setting that I can enable/disable to ignore and continue processing when these errors are encountered? I've read a little on "fail package on step failure" but not quite clear on it. Any tips? Thanks alot
    Thats strange,you should get an error for that in MSSQL 2000 also.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Can you post DDL (without editing) for the victim table..?

    DDL for both tables i.e. table in 2000 & table in 2005.

    I used to transfer data, but I didn't face such problem.
    Last edited by rajeshpatel; 10-18-06 at 01:17.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Is it possible that the PK was created with IGNORE_DUP_KEY=ON in 2000, but OFF in 2005?

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by jezemine
    Is it possible that the PK was created with IGNORE_DUP_KEY=ON in 2000, but OFF in 2005?
    No,that will also give an error of Duplicate key error in your DTS Package execution.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by rudra
    No,that will also give an error of Duplicate key error in your DTS Package execution.
    maybe I am missing something, but I don't see where the kimykimy said they are using DTS for anything. Or are you referring to this statement: "fail package on step failure"?

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by jezemine
    maybe I am missing something, but I don't see where the kimykimy said they are using DTS for anything. Or are you referring to this statement: "fail package on step failure"?
    exactly
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  8. #8
    Join Date
    Aug 2006
    Posts
    5
    thanks for your replies. You can ignore my statements on "fail package on step failure." I am not using DTS for the import. Its an insert statement created from a recordset in a vb application.

    In SQL 2000 when a duplicate record tries getting inserted it's ignored and moves on to the next record. In SQL 2005 when a duplicate record tries getting inserted I'm getting the "Duplicate Key" error. And the vb code is the same.

    Not sure if this helps, but previously this error was resolved by re-restoring the database. Could this be something that was overlooked during the restoration procedure?

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    if you are not using DTS, then my previous comment applies. This exact behavior can happen if you have a PK that was created with IGNORE_DUP_KEY=ON (in that case, dupes will be ignored and not inserted).

    so check if the PK is IGNORE_DUP_KEY=ON on the 2000 box and IGNORE_DUP_KEY=OFF on the 2005 box.

  10. #10
    Join Date
    Aug 2006
    Posts
    5
    I created a new index with the IGNORE_DUP_KEY=ON but when I'm getting an error message "Duplicate Key was ignored" when a dupilcate is encountered.

    What I don't understand is I have another table that retreives data in the same method but from a different source without IGNORE_DUP_KEY enabled and it ignores duplicates and continues processing.

    Is there a way to ignore this error message from appearing? Thanks

  11. #11
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by kimykimy
    I created a new index with the IGNORE_DUP_KEY=ON but when I'm getting an error message "Duplicate Key was ignored" when a dupilcate is encountered.

    What I don't understand is I have another table that retreives data in the same method but from a different source without IGNORE_DUP_KEY enabled and it ignores duplicates and continues processing.

    Is there a way to ignore this error message from appearing? Thanks
    Have you created that table in question?If not, then check for any trigger in that existing table...otherwise I think you should get an error ,and thats the way MSSQL works....
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •