Export (0) Print
Expand All
1 out of 9 rated this helpful - Rate this topic

Handling Database Engine Errors

Many errors raised by the SQL Server Database Engine can be captured and resolved programmatically. Error-handling capabilities are provided by the Transact-SQL language and by the data access application programming interfaces (APIs) that applications use to access data stored in the Database Engine.

Errors from the Database Engine can be handled on two levels:

  • Errors can be handled in the Database Engine by adding error-handling code to Transact-SQL batches, stored procedures, triggers, or user-defined functions. The Transact-SQL error-handling mechanisms include the TRY…CATCH construct (see TRY...CATCH (Transact-SQL)), the RAISERROR statement, and the @@ERROR function.
  • Errors can be returned to the calling application and handled in the application code. Each of the APIs that applications use to access the Database Engine provides mechanisms for passing the error information back to the application.

Topic Description

Understanding Database Engine Errors

Every Database Engine error contains the following attributes: an error number, a message string, severity, state, procedure name, and line number.

Retrieving Error Information in Transact-SQL

Transact-SQL code can retrieve information about errors using the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR functions.

Using TRY...CATCH in Transact-SQL

SQL Server 2005 introduces the ability to process errors in Transact-SQL code using a TRY…CATCH construct similar to the exception-handling features of the Microsoft Visual C++ and Visual C# languages. When an error condition is detected in a TRY block, control is passed to a CATCH block where it can be processed. This is the primary mechanism for handling errors in the SQL Server 2005 Database Engine.

Using RAISERROR

The RAISERROR statement can be used to raise user-defined errors. It can also be used from a CATCH block to pass on to the application errors processed in the CATCH block.

Using PRINT

The PRINT statement can be used to return user-defined messages to the application.

Using @@ERROR

In earlier versions of SQL Server, the @@ERROR function is the primary means of detecting errors in Transact-SQL statements. TRY…CATCH constructs provide improved functionality.

Handling Errors and Messages in Applications

Each of the data access APIs, such as ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC), have mechanisms for reporting to the application any error information received from the Database Engine.

Database Engine Error Severities

Database Engine errors and user-defined error messages are raised with severities from 1 to 25. sp_addmessage and RAISERROR can be used to generate user-defined error messages.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.