SQL Server Performance Forum – Threads Archive
Error handling in stored procs
In an sp, I am doing an insert. The insert fails because of a unique constraint on the table, and so @@ERROR becomes 2601. Server: Msg 2601, Level 14, State 3, Procedure parkers_vehsel_der_moveDerivative, Line 126 Note that this is severity 14, and so would not on its own raise an exception through ADO. In my stored procedure I quite happily trap this error, rollback the transaction, and raise a proper exception causing error, with severity 16. Back in my client app, it appears that the ADO only detects the first error, (severity 14, and so does not generate an exception). Is there any way to suppress the level 14 error, so that the 16 error is the only error to make it back to ADO, and hence raise an exception? A newsgroup search shows several people with the problem, but sadly no replies. ThanksSolved this now. Apparently there is a bug in the OLEDB provider on some ADO distributions where errors after the first error are not always picked up. <br />SET NOCOUNT ON is a rather unexpected workaround (but seems to work <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ />.
I personally believe it’s good to use SET NOCOUNT ON anyway, as it minimizes network traffic.
"How do you expect to beat me when I am forever?"
]]>