Server Side Error Handling - Part 2 (Errors and Error Messages)

  • Article

Each error in the server has two parts - an error message that describes the error that happened in the server and an error action that determines the effect of the error on the statement, level, batch, transaction, connection and/or server.

Server has instances of different combinations of errors and error messages. The major ones are:

  1. One error, one error message - This is the most common case and most intuitive.
  2. One error, two or more error messages - When an error is raised in a some special context, the first message has information about the error, while subsequent messages provides information about the context. This becomes an issue inside tsql try-catch. In the absence of any notion about error collection, the catch block is activated with one of the error messages. As sql server 2000 would have set @@error to the last error message, we decided to set error intrinsics (error_message(), etc.) to the last error message inside the catch block. In other words sql server 2005 would ignore all but the last error message inside tsql try-catch. Many systems would merge these error messages into one. One way to merge two error messages would be to provide the first message as a payload of the context message. Backward compatibility prevents us from mergeing old error messages. Care would be taken to not have multiple error messages for one error in future releases.
  3. Multiple errors, multiple error messages - In this situation the server displays as many information as possible before stopping execution. This is mostly useful in development when one wants to see all compile errors or permission failures before the execution is terminated. Inside tsql try-catch the ideal behavior would be to stop execution on first error and handle it in the catch block. This is what we have tried in sql server 2005. Note this is different from @@error behavior where it would be set to the last error.
  4. No error, one or more error messages - This happens when server recovers from non-severe errors and continues with exectuion. In reality these are informational or warning messages. They should have been sent with lower severity (10 to be precise), but can not be done because of backward compatibility. As clients do not expect to see any error messages inside tsql try-catch, they are silently ignored inside a try-block. The execution happens as if nothing has happened.

Once an error happens, server determines the action to take. Statement abort will abort the current statement. Level abort will terminate an executing proc, and will be visible as a statement abort in the scope of the caller. Batch aborts will terminate the batch. Transaction abort will abort the batch and rollback the transaction. Severe errors would kill the connection, while extremely severe error will shutdown the server.

Currently, the error action depends on the error message, the severity with which it is raised and the context in which it is raised. For tsql developer, it is very difficult to determine the error action for any error during development. Going forward we may enforce a rule where the error action just depends on the error message. To ensure that the error handling does not break on upgrades, this can only be done for new error messages.

In next post I will continue the discussion on certain error messages that are still relayed to clients from inside tsql tr-catch.

Thanks