Dissecting an Error Message in SQL Server

This is Part 3 of a series of blog posts by Data Education founder Adam Machanic on errors and exceptions in Microsoft SQL Server. The posts will cover everything from the TRY/CATCH syntax to the delicate relationship between transactions and exceptions. In Part 1, Adam gave a basic explanation of the difference between errors and exceptions. In Part 2, he examined types of exceptions. In this third installment, Adam breaks down the parts of the dreaded error message.

A SQL Server exception has a few different component parts, each of which is represented within the text of the error message. Each exception has an associated error number, error level, and state. Error messages can also contain additional diagnostic information including line numbers and the name of the procedure in which the exception occurred.

Error Number

The error number of an exception is represented by the text “Msg” within the error text. For example, the error number of the following exception is 156:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

SQL Server generally returns the error message with the exception, so having the error number usually doesn’t assist from a problem-solving point of view. However, there are times when knowing the error number can be of use. Examples include use of the @@ERROR function, or when doing specialized error handling using the TRY/CATCH syntax. (I’ll blog more about both of these soon.)

The error number can also be used to look up the templatized, localized text of the error in the sys.messages catalog view. The message_id column contains the error number, and the language_id column can be used to get the message in the correct language. The following T-SQL returns the English text for error 208:

SELECT text
FROM sys.messages
WHERE
    message_id = 208
    AND language_id = 1033

The output of this query is as shown here:

Invalid object name '%.*ls'.

Error Level

The Level tag within an error message indicates a number between 1 and 25. This number can sometimes be used to either classify an exception or determine its severity. Unfortunately, the key word is “sometimes”: the error levels as generated by SQL Server are highly inconsistent and should generally not be used in order to make decisions about exceptions.

The following exception, based on its error message, is of error level 15:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

The error levels for each exception can be queried from the sys.messages view, using the severity column. A severity of less than 11 indicates that a message is a warning. If severity is 11 or greater, the message is considered to be an error and can be broken down into the following documented categories:

  • Error levels 11 through 16 are documented as “errors that can be corrected by the user.” The majority of exceptions thrown by SQL Server are in this range, including constraint violations, parsing and compilation errors, and most other run time exceptions.
  • Error levels 17 through 19 are more serious exceptions. These include out-of-memory exceptions, disk space exceptions, internal SQL Server errors, and other similar violations. Many of these are automatically logged to the SQL Server error log when they are thrown. Those that are logged have a value of 1 for the is_event_logged column of sys.messages.
  • Error levels 20 through 25 are fatal connection and server-level exceptions. These include various types of data corruption, network, logging, and other critical errors. Virtually all of the exceptions at this level are automatically logged.

Although the error levels that make up each range are individually documented in Books Online, the documentation is inconsistent or incorrect in many cases. For instance, level 11 is documented as indicating that “the given object or entity does not exist.” However, error 208, “Invalid object name,” is a level-16 exception. Many other errors have equally unpredictable levels, and it is recommended that client software not be programmed to rely on the error levels for handling logic.

In addition to the levels themselves, there is for the most part no discernable pattern regarding error severities and whether the error will behave on the statement or batch level. For instance, both errors 245 (“Conversion failed”) and 515 (“Cannot insert the value NULL … column does not allow nulls”) are level-16 exceptions. However, 245 is a batch-level exception, whereas 515 acts at the statement level.

Error State

Each exception has a State tag, which contains information about the exception that is used internally by SQL Server. The values that SQL Server uses for this tag are not documented, so this tag is generally not helpful. The following exception has a state of 1:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.

If an exception does not occur within a procedure, the line number refers to the line in the batch in which the statement that caused the exception was sent.

Be careful not to confuse batches separated with GO with a single batch. Consider the following T-SQL:

SELECT 1
GO
SELECT 2
GO
SELECT 1/0
GO

In this case, although a divide-by-zero exception occurs on line 5 of the T-SQL itself, the exception will actually report line 1:

-----------
1

(1 row(s) affected)

-----------
2

(1 row(s) affected)

-----------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

The reason for the reset of the line number is that GO is not actually a T-SQL command. It’s an arbitrary identifier recognized by the SQL Server client tools (e.g., SQL Server Management Studio and SQLCMD). GO tells the client to separate the batches, sending each to SQL Server serially. So in the preceding example, SQL Server sees three individual batches of T-SQL, and does not know how many lines of code are displayed on the client side.

This seemingly erroneous line number is reported as such because each batch is sent separately to the query engine. SQL Server does not know that on the client (e.g., in SQL Server Management Studio), these batches are all joined together on the screen. As far as SQL Server is concerned, these are three completely separate units of T-SQL that happen to be sent on the same connection.

That covers errors messages for now. Next up: the RAISERROR function. Thanks for reading!