Chapter 11 - Error Messages

  • Article

When Microsoft SQL Server encounters a problem, it either writes a message from the sysmessages * *system table to the SQL Server error log and the Microsoft Windows NT application log, or sends it to the client, depending on the severity level.

Error messages can be either returned by SQL Server when encountering a problem, or produced manually using the RAISERROR statement.

The RAISERROR statement provides centralized error message management. RAISERROR can retrieve an existing entry from sysmessages, or it can use a hard-coded (user-defined) message. When RAISERROR returns a user-defined error message, it also sets a system variable to record that an error has occurred. The message can include C PRINTF-style format strings, which are filled with arguments specified by RAISERROR at run time. After it is defined, the message is sent back to the client as a server error message.

Whether returned by SQL Server or through the RAISERROR statement, each message contains:

  • A message number, which uniquely identifies the error message. 

  • A severity level, which provides an indication of the type of problem. 

  • An error state number, which identifies the source from which the error was issued (if the error can be issued from more than one place). 

  • A message, which states the problem and sometimes a possible solution to fixing it. 

For example, if you access a table that does not exist:

SELECT * 
FROM bogus

The error message sent to the client looks something like this:

Server: Msg 208, Level 16, State 1
Invalid object name 'bogus'.

You can view the list of SQL Server error messages by querying the sysmessages table in the master database.

For more information about sysmessages, see "System Error Messages" in this volume.

See Also 

In Other Volumes 

"RAISERROR" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Using RAISERROR" in Microsoft SQL Server Database Developer's Companion 

Error Message Formats

All of the Microsoft SQL Server components can issue informational, warning, or error messages to applications. Most SQL Server messages returned to applications have these parts:

  • Error number 

    A one-to-five-digit number that identifies the message. Error numbers for user-defined messages can contain more digits. 

  • Description 

    A Unicode string that contains information about the condition that generated the message. 

  • Severity level 

    A one-or two-digit number that indicates the severity of the error condition. 

  • State 

    A one-to three-digit number with a maximum value of 127, which indicates to Microsoft support engineers and developers the location in the SQL Server code that generated the message: 

  • Line number 

    The line number within the batch or stored procedure that contains the statement that generated the message. 

    Line number can also be within the text of the stored procedure that is being executed. 

The error numbers, descriptions, and severity levels for most SQL Server messages are stored in master.dbo.sysmessages. The state and line numbers are generated dynamically by the code issuing the message.

Messages raised in the client Net-Libraries, the Microsoft OLE DB Provider for SQL Server, or the SQL Server ODBC driver do not have some of these message parts.

An example of an error message can be seen by executing the statement:

SELECT * FROM ThisObjectDoesNotExist

This statement raises an error with these parts:

Error number: 208
Severity level: 16
State: 1
Line: 1
Description: Invalid object name 'ThisObjectDoesNotExist'.

All of the data APIs used by applications to access SQL Server return the error number and description. Not all of the APIs return the severity level, state, or line number. The OLE DB Provider for SQL Server and the SQL Server ODBC driver return these parts only if an OLE DB or ODBC application has been written to use SQL Server-specific diagnostic features exposed by the provider and driver.

Error Message Numbers and Descriptions

A message number uniquely identifies each error message and the error message text describes the problem. The error message text often includes placeholders for information (such as object names) to be inserted in the error message when it is displayed.

In the description column of the sysmessages table, a percent sign (%) followed by a character serves as a placeholder; the specific data is supplied when the error message is generated. The notation %d is a placeholder for a number; %ls (or %.*ls) is a placeholder for a string. For example, the actual error message displayed for error 105 might be:

Unclosed quote before the character string %.*ls.

When you report an error to your primary support provider, it is important to include error numbers, error states, object types, and object names. Otherwise, it can be difficult and time-consuming for the support provider to render assistance in resolving the error message.

See Also 

In This Volume 

Reporting Errors to Your Primary Support Provider

In Other Volumes 

"sysmessages" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Error Message Severity Levels

The severity level of an error message provides an indication of the type of problem that Microsoft SQL Server has encountered.

Messages with a severity level of 10 are informational messages and indicate problems caused by mistakes in the information you have entered. Severity levels from 11 through 16 are generated by the user, and can be corrected by the user.

Severity levels from 17 through 25 indicate software or hardware errors. You should inform the system administrator whenever problems that generate errors with severity levels 17 and higher occur. The system administrator must resolve these errors and track their frequency. When a level 17, 18, or 19 error occurs, you can continue working, although you might not be able to execute a particular statement.

The system administrator should monitor all problems that generate severity levels from 17 through 25 and print the error log, which contains information to backtrack from the error.

If the problem affects an entire database, you can use DBCC CHECKDB (database consistency checker) to determine the extent of the damage. DBCC may identify some objects that must be removed, and will optionally repair the damage. If damage is extensive, the database might have to be restored.

When specifying user-defined error messages with RAISERROR, use error message numbers greater than 50000 and severity levels from 0 through 18. Only system administrators can issue RAISERROR with a severity level from 19 through 25.

Severity Levels 0 through 19

Error messages with a severity level of 10 are informational. Error messages with severity levels from 11 through 16 are generated by user and can be corrected by the user. Severity levels from 17 and 18 are generated by resource or system errors; the user's session is not interrupted.

Using sp_addmessage, user-defined messages with severities from 1 through 25 can be added to sysmessages. Only the system administrator can add messages with severities from 19 through 25.

Error messages with severity levels 17 and higher should be reported to the system administrator.

Severity Level 10: Status Information

Severity level 10 is an informational message and indicates a problem caused by mistakes in the information you have entered. Severity level 10 is not visible in SQL Server 7.0.

Severity Levels 11 through 16 

These messages indicate errors that can be corrected by the user.

Severity Level 17: Insufficient Resources 

These messages indicate that the statement caused SQL Server to run out of resources (such as locks or disk space for the database) or to exceed some limit set by the system administrator.

Severity Level 18: Nonfatal Internal Error Detected 

These messages indicate that there is some type of internal software problem, but the statement finishes, and the connection to SQL Server is maintained. For example, a level 18 message occurs when the SQL Server query processor detects an internal error during query optimization.The system administrator should be informed every time a severity level 18 message occurs.

Severity Level 19: SQL Server Error in Resource 

These messages indicate that some nonconfigurable internal limit has been exceeded and the current batch process is terminated. Level 19 errors occur rarely; however, they must be corrected by the system administrator or your primary support provider.The administrator should be informed every time a severity level 19 message occurs.

Severity Levels 20 through 25

Severity levels from 20 through 25 indicate system problems. These are fatal errors, which means that the process (the program code that accomplishes the task specified in your statement) is no longer running. The process freezes before it stops, records information about what occurred, and then terminates. The client connection to SQL Server closes, and depending on the problem, the client might not be able to reconnect.

Error messages with a severity level of 19 or higher stop the current batch. Errors messages with a severity level of 20 or higher are considered fatal errors and terminate the client connection. Errors messages in this range may affect all of the processes in the database, and may indicate that a database or object is damaged. Error messages with a severity level from 19 through 25 are written to the error log.

Severity Level 20: SQL Server Fatal Error in Current Process 

These messages indicate that a statement has encountered a problem. Because the problem has affected only the current process, it is unlikely that the database itself has been damaged.

Severity Level 21: SQL Server Fatal Error in Database (dbid) Processes 

These messages indicate that you have encountered a problem that affects all processes in the current database. However, it is unlikely that the database itself has been damaged.

Severity Level 22: SQL Server Fatal Error Table Integrity Suspect 

These messages indicate that the table or index specified in the message has been damaged by a software or hardware problem.Level 22 errors occur rarely; however, if you should encounter one, run DBCC CHECKDB to determine if other objects in the database are also damaged. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. To continue working, you must reconnect to SQL Server. Otherwise, use DBCC to repair the problem. In some cases, it may be necessary to restore the database.If restarting does not help, the problem is on the disk. Sometimes it can be solved by destroying the object specified in the error message. For example, if the message tells you that SQL Server has found a row with a length of 0 in a nonclustered index, delete the index and rebuild it.

Severity Level 23: SQL Server Fatal Error: Database Integrity Suspect 

These messages indicate that the integrity of the entire database is suspect due to damage caused by a hardware or software problem.Level 23 errors occur rarely; however, if you should encounter one, run DBCC CHECKDB to determine the extent of the damage. It is possible that the problem is in the cache only and not on the disk itself. If so, restarting SQL Server corrects the problem. To continue working, you must reconnect to SQL Server. Otherwise, use DBCC to repair the problem. In some cases, it may be necessary to restore the database.

Severity Level 24: Hardware Error 

These messages indicate some type of media failure. The system administrator might have to reload the database. It might also be necessary to call your hardware vendor.

See Also 

In Other Volumes 

"Backing Up and Restoring Databases" in Microsoft SQL Server Administrator's Companion 

"DBCC" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"DBCC CHECKDB" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"Setting Configuration Options" in Microsoft SQL Server Administrator's Companion 

"sp_configure" in Microsoft SQL Server Transact-SQL and Utilities Reference 

ADO Error Message Format

The ADO specification defines Error objects in an Errors collection. Each Error object holds a message from the underlying provider. ADO does not provide for provider-specific diagnostic information. ADO and the Microsoft OLE DB Provider for SQL Server map the parts of Microsoft SQL Server messages into these ADO Error object properties:

  • Description 

    Contains the SQL Server message description, usually from the description column in master.dbo.sysmessages. 

  • SQLState 

    Contains the five-character SQLSTATE code generated by the OLE DB Provider for SQL Server. 

  • NativeError 

    Contains the SQL Server error number. For example, if a statement raises a SQL Server error 170 (syntax error), 170 is returned in NativeError. 

See Also 

In Other Volumes 

"Errors Collection and Error Object" in Microsoft SQL Server Building Applications 

OLE DB Error Message Format

OLE DB applications receive Microsoft SQL Server messages in two ways:

  • Call the OLE DB ISQLErrorInfo::GetSQLInfo function. 

  • Call the provider-specific ISQLServerErrorInfo::GetErrorInfo function. 

ISQLErrorInfo::GetSQLInfo returns the SQL Server error numbers from master.dbo.sysmessages as the plNativeError parameter and the SQLSTATE value as the pbstrSQLState parameter. These SQLSTATE codes are not related to any of the parts of a SQL Server message. The Microsoft OLE DB Provider for SQL Server generates the appropriate SQLSTATE code anytime it returns a message to an application. The SQLSTATE codes generated by the OLE DB Provider for SQL Server are same as the five-character SQLSTATE codes defined in the ODBC specification. For ISQLErrorInfo::GetSQLInfo, pbstrSQLState may be NULL when the error is not produced by SQL Server.

Both the OLE DB Provider for SQL Server and the Microsoft OLE DB Provider for ODBC support the ISQLErrorInfo interface.

The provider-specific ISQLServerErrorInfo interface returns more detail about a SQL Server error. The ISQLServerErrorInfo interface exposes one member function, GetErrorInfo. The function returns a pointer to a pointer to an SSERRORINFO structure and a pointer to a string buffer. The pointer to pointer to SSERRORINFO structure is NULL when the error is not produced by SQL Server.

The SQL Server message parts map to members of SSERRORINFO structure as described below:

pwszMessage 

Contains the SQL Server error description.

lNative 

Contains the SQL Server error number.

bState 

Contains the SQL Server error state.

bClass 

Contains the severity of the SQL Server error condition.

wLineNumber 

Contains the line number of the stored procedure on which the error occurred.

See Also 

In Other Volumes 

"Information in OLE DB Error Interfaces" in Microsoft SQL Server Building Applications 

ODBC Error Message Format

ODBC drivers return messages to applications as diagnostic records. An application can call the SQLGetDiagRec and SQLGetDiagField functions to retrieve these diagnostic records. The Microsoft SQL Server ODBC driver maps SQL Server message parts into these standard ODBC diagnostic record fields:

SQLSTATE

The ODBC specification defines a set of five-character codes called SQLSTATE codes that identify the conditions generating the message. These SQLSTATE codes are not related to any of the parts of a SQL Server message. The SQL Server ODBC driver generates the appropriate SQLSTATE code anytime it returns a message to an application.

pfNative 

The SQL Server ODBC driver returns the SQL Server error number as the ODBC pfNative field. For example, if a statement raises a SQL Server error 170 (syntax error), the ODBC driver returns 170 in pfNative.

MessageText 

The SQL Server ODBC driver returns the SQL Server error description as the MessageText field in an ODBC diagnostic record. The ODBC specification defines a series of headers for the MessageText field that indicates the component that issued the message:

  • [Microsoft][ODBC Driver Manager] 

    These messages are issued by the ODBC Driver Manager. 

  • [Microsoft][ODBC Cursor Library] 

    These messages are issued by the ODBC client cursor library. 

  • [Microsoft][ODBC SQL Server Driver] 

    These messages are issued by the SQL Server ODBC driver. If there are no other nodes with the name of either a Net-Library or SQL Server installation, then the message was issued by the driver. 

  • [Microsoft][ODBC SQL Server Driver][Net-Libraryname] 

    These messages are issued by the SQL Server Net-Library, where Net-Libraryname is the display name of a SQL Server client Net-Library (for example, Named Pipes, Shared Memory, Multiprotocol, TCP/IP Sockets, NWLink IPX/SPX, or Banyan VINES). 

  • [Microsoft][ODBC SQL Server Driver][SQL Server] 

    These messages are issued by SQL Server. The remainder of the error message is the description from SQL Server, usually from master.dbo.sysmessages. 

The ODBC specification allows ODBC drivers to define driver-specific fields in ODBC diagnostic records. The SQL Server ODBC driver maps SQL Server message parts into these SQL Server ODBC driver-specific diagnostic fields:

SQL_DIAG_SS_SEVERITY.

Contains the SQL Server severity level.

SQL_DIAG_SS_MSGSTATE.

Contains the SQL Server state. It is not related to the ODBC SQLSTATE code.

SQL_DIAG_SS_LINE.

Contains the number of the line containing the SQL statement generating the message.

SQL_DIAG_SS_PROCNAME.

Contains the name of the stored procedure generating the message, if appropriate.

SQL_DIAG_SS_SRVNAME.

Contains the name of the server from which the message came.

See Also 

In Other Volumes 

"Handling Errors and Messages" in Microsoft SQL Server Building Applications 

Embedded SQL for C Error Message Format

The errors and messages are returned to the application in an SQLCA data structure. The Microsoft SQL Server message parts map to fields in SQLCA data structure as described below:

sqlerrmc 

Contains text of the error message.

sqlerrd[1] 

Contains the SQL Server error number.

sqlerrd[2] 

Contains the SQL Server severity level.

Sqlstate 

Contains the SQLSTATE run-time error codes as defined in Embedded SQL for C. These SQLSTATE codes are not related to any of the parts of a SQL Server message.

DB-Library Error Message Format

DB-Library returns Microsoft SQL Server errors and messages to call-back functions, written by the application programmer.

The application programmer uses the dberrorhandle function to give DB-Library the address of the call-back function that handles the errors. When DB-Library determines that an error has occurred, it calls the call-back function identified by dberrorhandle. DB-Library passes the SQL Server error information into the parameters of the call back function as described below:

severity 

Contains the severity of the error.

dberr 

Contains the SQL Server error number.

dberrstr 

Contains the description of the SQL Server error.

The application programmer uses the dbmsghandle function to give DB-Library the address of the call-back function that handles the messages. When DB-Library receives an informational message from SQL Server, it calls the call-back function identified by dbmsghandle.

DB-Library passes the SQL Server message information into the parameters of the call-back function as described below:

msgno 

Contains the error number identifying the message.

msgstate 

Contains the SQL Server message state.

severity 

Contains the severity of the error condition.

msgtext 

Contains the description of the SQL Server message.

srvname 

Contains the server name that generated the message.

procname 

Contains the stored procedure name that generated the message.

line 

Contains the line number in the stored procedure or the command batch that generated the message.

DB-Library calls the application error handler and message handler functions asynchronously as packets containing messages and errors are received from the server. This means DB-Library applications may receive errors and messages in a slightly different sequence than applications using either the OLE DB Provider for SQL Server or the SQL Server ODBC Driver.

Messages Returned by SQL Server 7.0 Utilities

All of the Microsoft SQL Server version 7.0 utilities use ODBC except for the isql command prompt utility. This has two effects on the way error messages are displayed by the ODBC-based utilities:

  • The error messages may have leading ODBC headers built into the description. These headers identify the component raising the error. The osql command prompt utility returns the ODBC headers. SQL Server Query Analyzer defaults to removing the ODBC headers, but users can set a connection option so that these are returned. 

    For example, isql returns the error 208 description as: 

    Invalid object name 'ThisObjectDoesNotExist'.

**osql** includes the ODBC headers in the description: 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid

object name 'ThisObjectDoesNotExist'. 

The presence of the ODBC headers in the message does not necessarily indicate that the problem is in the ODBC components. The SQL Server ODBC driver includes these headers in messages from all components. For more information about how to determine the component raising the error, see "ODBC Error Message Format" in this volume.

  • Errors raised within the SQL Server ODBC driver have only a description. They have no error number, state, severity level, or line number. For example, if the following statement is executed in SQL Server Query Analyzer, the SQL Server ODBC driver itself raises a syntax error: 

    { CLL sp_who }

Because the error is generated by the driver, the only message part displayed by SQL Server Query Analyzer is the description: 

<pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">[Microsoft][SQL Server ODBC Driver]Syntax error or access violation

See Also 

In Other Volumes 

"isql Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"osql Utility" in Microsoft SQL Server Transact-SQL and Utilities Reference 

"sysmessages" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Finding Supplemental Error Message Information

Over time, more information about error messages either documented in this section or not documented in this section may become available. For current error message information, see the "SQL Server version 7.0 Updated Error Message Information" button at https://www.microsoft.com/isapi/redir.dll?prd=support&ar=sqlbook .

Adding User-Defined Error Messages

User-defined error messages can be added to the sysmessages table using the system stored procedure sp_addmessage. At a minimum, you can specify the message number, the severity level, and the message text using sp_addmessage.

Similar to specifying user-defined error messages with RAISERROR, use error message numbers greater than 50000 and severity levels from 0 through 18. Only system administrators can issue RAISERROR with a severity level from 19 through 25.

See Also 

In Other Volumes 

"sp_addmessage" in Microsoft SQL Server Transact-SQL and Utilities Reference 

Cc917588.spacer(en-us,TechNet.10).gif