asked
Error Handling for Error_Severity = 20-25
I've read that Try.. Catch will not capture an error with Error_Severity() between 20-25. I am wondering if there is another way to test a piece of code for this kind of error and capture it, so that the error can be logged instead of ending the procedure. For instance, I have a procedure that contains dynamic code. This code is building an execute statement, pulling a Procedure Name and ParameterList from a table. If one of the 'EXEC <> <>' fails, I want to log it and move on to the next procedure in the table. I don't want my code to fail. Is there a way to test for this before actually executing it, which fails with a high severity and ends my procedure? My example is if someone puts bogus parameters in the ParameterList, causing the EXEC of a specific procedure to fail. Thank you in advance!
From [This MSDN page][1]: Severity levels 20-24 "Indicate system problems and are fatal errors, which means that the Database Engine task that is executing a statement or batch is no longer running. The task records information about what occurred and then terminates. In most cases, the application connection to the instance of the Database Engine may also terminate. If this happens, depending on the problem, the application might not be able to reconnect." Thus, there is nothing to continue executing your query, so the issue can't be bypassed. It is worth noting these are not usually code errors, but systemic issues which your code couldn't correct anyway. You should ensure that such errors are handled elegantly by a calling application. The same article clarifies the position for TRY/CATCH handling of these: "Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates." There's a slightly more detailed breakdown of the types of issue that can result in errors with high severity levels [here][2], which may help you to understand better why they're not able to be handled by TRY/CATCH. [1]:
https://msdn.microsoft.com/en-GB/library/ms164086.aspx [2]:
http://sqlperformance.com/2015/04/sql-performance/dealing-with-high-severity-errors-in-sql-server
20 People are following this question.
