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 alotHey 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.|||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.|||Is it possible that the PK was created with IGNORE_DUP_KEY=ON in 2000, but OFF in 2005?|||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.|||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"?|||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 ;)|||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?|||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.|||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|||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...sql