Freigeben über

Server Side Error Handling - Part 3 (Why do I still see error messages from inside tsql try-block?)

  • Artikel

There are cases when you see error messages relayed from inside the try-block. Some are intuitive and some are not.

  • Compile Errors - These errors can simply not be caught. This is true for any programming language. When compilation of a program generates an error it can not be caught by the enclosing try-block. As the batch has not started execution, there is no way BEGIN TRY would take affect. I am always asked how I differentiate between compile and non compile errors. Some errors like syntax errors are obvious, some are not. One way to figure it out is to put a PRINT statement immediately after the BEGIN TRY statement. If the message is not printed and you see an error message, it is a compile error and can not be caught.

BEGIN TRY
   PRINT 'Inside Try-Block'
   SELECT ** FROM T  /* will cause syntax error */
END TRY
BEGIN CATCH
   SELECT error_number() as 'Error In Try Block'
END CATCH

A compile error generated in the lower scope would be caught. This is because the compile of the lower scope happens during the execution of the enclosing scope (more specifically after the execution of BEGIN TRY).

BEGIN TRY
   PRINT 'Inside Try-Block'
   EXEC ('SELECT ** FROM T ) /* compile error in the lower scope */
END TRY
BEGIN CATCH
   SELECT error_number() as 'Error In Try Block'
END CATCH

  Note that in this case you will see the 'Inside Try-Block' message getting printed.

  • Recompile Errors - They are raised when a statement recompiles because of change in schema, environment, statistics, etc. They are similar to compile errors, except that they would be raised after BEGIN TRY has executed. We debated on weather to catch such errors or not. Finally we decided against catching them to keep it consistent with compile errors. The decision was also influenced by the fact that on recompile we exit the execution driver in the SQL  Server and enter the compilation module to recompile the statement. This behavior is not guaranteed in future releases. We may decide to catch these errors as the BEGIN TRY was executed and the handler was activated.
  • Remote execution of a Procedure - When a procedure/batch (pass-through) is executed remotely (through four-part name or EXECUTE AT), the error handling on the local server depends on the type of remote server and the error action at the remote server if it is a SQL Server.
    • If the remote server is not a SQL Server, it is difficult to parse the error message for its processing at the local server. Such error message from the remote server is relayed to the client without any processing at the local server. The local server though detects that an error happened on remote execution and it would raise a generic error. This generic error will be handled by the local tsql try-catch.
    • If the remote server is SQL Server, the local server tries to find out if the remote execution resulted in batch abort or higher. If remote execution was aborted then the error causing the abort can be handled at the local server. All previous error messages are relayed. In the absence of batch abort, all error messages are relayed to the client without any processing at the local server. This behavior is consistent with @@error behavior in prior release of SQL Server. @@error was set if and only if the remote execution was aborted. We would like to have the same behavior for non-SQL Servers, but the oledb does not have any API to detect the error action at the server.
  • Attentions - Attentions are sent when a running query is cancelled. The server detects an attention and aborts the execution. There was a considerable debate on whether to catch attentions or not. Finally it was decided not to catch them (except for DTC attentions). Attentions (excluding DTC attention) do not result in an error message. If they were caught the error intrinsics would not be populated inside the catch block and no logic based on error number could be written.

This concludes my talk on server side error handling. If you have any questions or comments please feel free to send them my way.

Thanks

Comments