ignore dup key warning

[Andre]

Is it possible to suppress the warning that is generated when you have an
index with IGNORE_DUP_KEY and you try to insert duplicate records? The
warning is causing our jobs, such as DTS, to fail because it's not a
success code.

Thanks, Andre

[Plamen Ratchev]

If you are using SQL Server 2000 then I do not think there is a way to suppress the warning. On SQL Server 2005/2008 you
can use TRY/CATCH to trap it.

However, the real solution is to avoid using IGNORE_DUP_KEY and to properly code your SQL code to avoid duplicates (for
example, using a predicate with NOT EXISTS to insert only keys that do not exist in the table).

--
Plamen Ratchev
http://www.SQLStudio.com

[Andre]

Thanks for the response. We'll look at try/catch as we're in SQL 2008.

Andre

[Erland Sommarskog]

Plamen Ratchev (Pla...@SQLStudio.com) writes:
> If you are using SQL Server 2000 then I do not think there is a way to
> suppress the warning. On SQL Server 2005/2008 you can use TRY/CATCH to
> trap it.

I don't think you can, because it is only a warning.

But on SQL 2000 the message is raised with level 16 - without setting
@@error.

Andr� mentioned DTS, maybe DTS thinks it's an error too. In that
case, more orthodox coding is to recommend. (Personally, I don't
use IGNORE_DUP_KEY very often, but I did just it in a UDF that I
posted recently, because I needed to make sure that the UDF returns
unique values, but I knew the duplicates would only be occasional.)

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Plamen Ratchev]

You are correct Erland. I did not realize it was only a warning and TRY/CATCH cannot trap it.

I have never used IGNORE_DUP_KEY in production code. Much better to code the SQL statements correct.