Handling Errors and Messages in Applications

Errors raised either by Microsoft® SQL Server™ 2000 or the RAISERROR statement are not part of a result set. Errors are returned to applications through an error handling mechanism separate from the processing of result sets.

Each database API has some set of functions, interfaces, methods, objects or structures through which they return errors and messages. Each API function or method typically returns a status code indicating the success of that operation. If the status is anything other than success, the application can call the error functions, methods, or objects to retrieve the error information.

SQL Server actually has two mechanisms for returning error information:

• Errors
  • The errors from sysmessages with a severity of 11 or higher.
    
    
  • Any RAISERROR statement with a severity of 11 or higher.
• Messages
  • The output of the PRINT statement.
    
    
  • The output of several DBCC statements.
    
    
  • The errors from sysmessages with a severity of 10 or lower.
    
    
  • Any RAISERROR statement with a severity of 10 or lower.

Applications using APIs such as ADO and OLE DB cannot generally distinguish between errors and messages. In ODBC applications, messages generate a SQL_SUCCESS_WITH_INFO function return code, and errors usually generate a SQL_ERROR return code. The difference is most pronounced in DB-Library, in which errors are returned to the application error handler function, and messages are returned to the application message handler function.

ODBC Error Handling

The ODBC specification introduced an error model that has served as the foundation of the error models of the generic database APIs such as ADO, OLE DB, and the APIs built over ODBC (RDO, DAO, and the MFC Database Classes). In the ODBC model, errors have the following attributes:

• SQLSTATE

  The SQLSTATE is a five-character error code defined originally in the ODBC specification. SQLSTATEs are common across all ODBC drivers and provide a way for applications to code basic error handling without testing for all the different error codes returned by various databases. The ODBC SQLSTATE has nothing to do with the state attribute of SQL Server error messages.

  ODBC 2.x returns one set of SQLSTATE codes, and ODBC 3.x returns a set of SQLSTATE codes aligned with the X/Open Data Management: Structured Query Language (SQL), version 2 standard. Because all ODBC drivers return the same sets of SQLSTATE codes, applications basing their error handling on SQLSTATE codes are more portable.

• Native error number

  The native error number is the error number from the underlying database. ODBC applications receive the SQL Server error numbers as native error numbers.

• Error message string

  The error message is returned in the error message string parameter.

When an ODBC function returns a status other than SQL_SUCCESS, the application can call SQLGetDiagRec to get the error information. For example, if an ODBC application gets a syntax error (SQL Server error number 170), SQLGetDiagRec returns:

szSqlState = 42000, pfNative = 170
szErrorMsg =
'[Microsoft][ODBC SQL Server Driver][SQL Server]
                                     Line 1: Incorrect syntax near *'

The ODBC SQLGetDiagField function allows ODBC drivers to specify driver-specific diagnostic fields in the diagnostic records returned by the driver. The SQL Server ODBC driver specifies driver-specific fields to hold SQL Server error information such as the SQL Server severity and state codes.

For more information about retrieving error messages in ODBC applications, see Handling Errors and Messages.

ADO Error Handling

ADO uses an Errors object and Errors collection to return standard error information such as SQLSTATE, native error number, and the error message string. These are the same as their ODBC counterparts. ADO does not support any provider-specific error interfaces, so SQL Server-specific error information such as the severity or state are available to ADO applications.

For more information about retrieving error messages in ADO applications, see Handling Errors and Messages in ADO.

OLE DB Error Handling

OLE DB uses the IErrorInfo interface to return standard error information such as the SQLSTATE, native error number, and error string. These are the same as their ODBC counterparts. The Microsoft OLE DB Provider for SQL Server defines an ISQLServerErrorInfo interface to return SQL Server-specific information such as the severity, state, procedure name, and line number.

For more information about retrieving error messages in OLE DB applications, see Errors.

DB-Library Error Handling

DB-Library uses a different mechanism for returning error information to an application. An application defines two call-back functions, an error handler and a message handler. When the DB-Library dynamic-link library has errors or messages to return, it calls the application's error handler or message handler function. Because DB-Library is specific to SQL Server, all SQL Server error information is available in the error and message handlers. DB-Library returns PRINT messages and low-severity error or RAISERROR messages to the message handler. High severity errors and RAISERROR messages are returned to the error handler function.

For more information about DB-Library error handling see Error and Message Handling.