Tips and Tricks for:  SQL Server, OLEDB

The Problem:   Level 16 Errors Can't always be handled by User Code

The Books-OnLine (BOL) is wrong.  It says that errors with Level 16  and below can be handled by user code.  In particular by examining @@Error.  Unfortunately, level 16 errors can not always be trapped.  Instead, they sometimes end the connection.

Here a sample LEVEL 16 error that can't be trapped and just ends the connection:

Server: Msg 7399, Level 16, State 1, Procedure usp_Quote_Sync_DETAIL, Line 16 OLE DB provider 'IBMDA400' reported an error.
[OLE/DB provider returned message: CPF5026: Duplicate key not allowed for member DETAIL.]

This came when performing a INSERT using a Linked server to a DB2 database on an iSeries (f.k.a. AS/400) computer.

Solution: Partial Soluton is to use seperate stored procedure but it doesn't always work.

The best available solution is to put the code that gets the Level 16 error into its own stored procedure.  Sometimes this allows the code in the calling stored procedure to examine @@Error after the SP with the problem completes.  Unfortunately, this doesn't always work.  Sometimes level 16 errors end the connection.  I don't have a solution that handles them all.