DATABASE

SQL Server 2005 : How Exceptions Work in SQL Server

10/12/2010 9:41:40 AM
The first step in understanding how to handle errors and exceptions in SQL Server is to take a look at how the server itself deals with error conditions. Unlike many other programming languages, SQL Server has an exception model that involves different behaviors for different types of exceptions. This can cause unexpected behavior when error conditions do occur, so careful programming is essential when dealing with T-SQL exceptions.

To begin with, think about connecting to a SQL Server and issuing some T-SQL. First, you must establish a connection to the server by issuing login credentials. The connection also determines what database will be used as the default for scope resolution (i.e., finding objects—more on this in a bit). Once connected, you can issue a batch of T-SQL. A batch consists of one or more T-SQL statements, which will be compiled together to form an execution plan.

The behavior of the exceptions thrown by SQL Server mostly follows this same pattern. Depending on the exception, a statement, a batch, or an entire connection may be aborted. Let's take a look at some examples to clarify what this means.

Statement-Level Exceptions

A statement-level exception aborts only the current statement that is running within a batch of T-SQL, allowing the subsequent statements within the batch to run. To see this behavior, use SQL Server Management Studio to execute a batch that includes an exception, followed by a PRINT statement. For instance:

SELECT POWER(2, 32)
PRINT 'This will print!'
GO

Running this batch results in the following output:

Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 4294967296.000000.
This will print!

When this batch was run, the POWER(2, 32) caused an integer overflow, which threw the exception. However, only the SELECT statement was aborted. The rest of the batch continued to run, which in this case means that the PRINT statement printed its message.

Batch-Level Exceptions

Unlike a statement-level exception, a batch-level exception does not allow the rest of the batch to continue running. The statement that throws the exception will be aborted, and any remaining statements in the batch will not be run. An example of a batch-aborting exception is an invalid conversion, such as the following:

SELECT CONVERT(INT, 'abc')
PRINT 'This will NOT print!'
GO

The output of this batch is as follows:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'abc' to data type int.

In this case, the conversion exception occurred in the SELECT statement, which aborted the entire batch. The PRINT statement was not allowed to run.

Batch-level exceptions might be easily confused with connection-level exceptions (which drop the connection to the server), but after a batch-level exception, the connection is still free to send other batches. For instance:

SELECT CONVERT(INT, 'abc')
GO
PRINT 'This will print!'
GO

In this case there are two batches sent to SQL Server, separated by the batch separator, GO. The first batch throws a conversion exception, but the second batch is still run. This results in the following output:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'abc' to data type int.
This will print!

Batch-level exceptions do not affect only the scope in which the exception occurs. The exception will bubble up to the next level of execution, aborting every call in the stack. This can be illustrated by creating the following stored procedure:

CREATE PROCEDURE ConversionException
AS
BEGIN
SELECT CONVERT(INT, 'abc')
END
GO

Running this stored procedure with a PRINT shows that even though the exception occurred in an inner scope (within the stored procedure), the outer batch is still aborted:

EXEC ConversionException
PRINT 'This will NOT print!'
GO

The result of this batch is the same as if no stored procedure was used:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'abc' to data type int.

Parsing and Scope-Resolution Exceptions

Exceptions that occur both during parsing and during the scope-resolution phase of compilation appear at first to behave just like batch-level exceptions. However, they actually have a slightly different behavior. If the exception occurs in the same scope as the rest of the batch, these exceptions will behave just like a batch-level exception. If, on the other hand, an exception occurs in a lower level of scope, these exceptions will behave just like statement-level exceptions—at least, as far as the outer batch is concerned.

As an example, consider the following batch, which includes a malformed SELECT statement (this is a parse exception):

SELECTxzy FROM SomeTable
PRINT 'This will NOT print!'
GO

In this case, the PRINT statement is not run because the whole batch is discarded during the parse phase. The output is the following exception:

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

To see the difference in behavior, the SELECT statement can be executed as dynamic SQL using the EXEC function. This causes the SELECT statement to execute in a different scope, showing the change in behavior from batch-like to statement-like. The following T-SQL can be run to observe the change:

EXEC('SELECTxzy FROM SomeTable')
PRINT 'This will print!'
GO

The PRINT statement is now executed, even though the exception occurred:

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

This type of exception also occurs during scope resolution. Essentially, SQL Server processes queries in two phases. The first phase parses and validates the query and ensures that the T-SQL is well formed. The second phase is the compilation phase, during which an execution plan is built and objects referenced in the query are resolved. If a query is submitted to SQL Server via ad hoc SQL from an application or dynamic SQL within a stored procedure, these two phases happen together. However, within the context of stored procedures, SQL Server exploits late binding. This means that the parse phase happens when the stored procedure is created, and the compile phase (and therefore scope resolution) occurs only when the stored procedure is executed.

To see what this means, create the following stored procedure (assuming that a table called SomeTable does not exist in the current database):

CREATE PROCEDURE NonExistantTable
AS
BEGIN
SELECT xyz
FROM SomeTable
END
GO

Although SomeTable does not exist, the stored procedure is created—the T-SQL parses without any errors. However, upon running the stored procedure, an exception is thrown:

Msg 208, Level 16, State 1, Procedure NonExistantTable, Line 4
Invalid object name 'SomeTable'.

Like the parse exception, scope-resolution exceptions behave similarly to batch-level exceptions within the same scope, and similarly to statement-level exceptions in the outer scope. Since the stored procedure creates a new scope, hitting this exception within the procedure aborts the rest of the procedure, but any T-SQL encountered in the same batch after execution of the procedure will still run. For instance:

EXEC NonExistantTable
PRINT 'This will print!'
GO

Connection and Server-Level Exceptions

The remaining types of exceptions that can be thrown by SQL Server are those that abort the entire connection and those that cause the server itself to crash. These types of exceptions are generally caused by internal SQL Server bugs and are, thankfully, quite rare. At the time of this writing, I cannot provide any examples of these types of exceptions, as I am not aware of any conditions in SQL Server 2005 that cause them.

The XACT_ABORT Setting

Although users do not have much control over the behavior of exceptions thrown by SQL Server, there is one setting that can be modified on a per-connection basis. Turning on the XACT_ABORT setting makes all statement-level, parsing, and scope-resolution exceptions behave like batch-level exceptions. This means that control will always be immediately returned to the client any time an exception is thrown by SQL Server.[]

[] This assumes that the exception is not handled.

To enable XACT_ABORT for a connection, the following T-SQL is used:

SET XACT_ABORT ON

This setting will remain enabled for the connection—even if it was set in a lower level of scope, such as in a stored procedure or dynamic SQL—until it is disabled using the following T-SQL:

SET XACT_ABORT OFF

To illustrate the effect of this setting on the behavior of exceptions, let's review a couple of the exceptions already covered. Recall that the following integer overflow exception operates at the statement level:

SELECT POWER(2, 32)
PRINT 'This will print!'
GO

Enabling the XACT_ABORT setting before running this T-SQL changes the output, resulting in the PRINT statement not getting executed:

SET XACT_ABORT ON
SELECT POWER(2, 32)
PRINT 'This will NOT print!'
GO

The output from running this batch is as follows:

Msg 232, Level 16, State 3, Line 2
Arithmetic overflow error for type int, value = 4294967296.000000.

Another example is a parsing exception in a lower scope. Recall that by default, the following exception does not abort the outer batch, but only the EXEC function:

EXEC('SELECTxzy FROM SomeTable')
PRINT 'This will print!'
GO

Just like the overflow exception, with XACT_ABORT set, the outer batch will be aborted in addition to the EXEC function, resulting in the PRINT statement not being evaluated.

In addition to controlling exception behavior, XACT_ABORT also modifies how transactions behave when exceptions occur.

Dissecting an Error Message

A SQL Server exception has a few different component parts, each of which are 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 .

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'.

See the section "SQL Server's RAISERROR Function" for more information about error message templates.

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.

    [] SQL Server 2005 Books Online, "Database Engine Error Severities," http://msdn2.microsoft.com/en-us/library/ms164086.aspx, December 2005.

  • 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.

[] Ibid.

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'.

Additional Information

In addition to the error number, level, and state, many errors also carry additional information about the line number on which the exception occurred and the procedure in which it occurred, if relevant. The following error message indicates that an invalid object name was referenced on line 4 of the procedure NonExistantTable:

Msg 208, Level 16, State 1, Procedure NonExistantTable, Line 4
Invalid object name 'SomeTable'.

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.

SQL Server's RAISERROR Function

In addition to the exceptions that SQL Server itself throws, users can raise exceptions within T-SQL by using a function called RAISERROR. The general form for this function is as follows:

RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

The first argument can be an ad hoc message in the form of a string or variable, or a valid error number from the message_id column of sys.messages. If a string is specified, it can include format designators that can then be filled using the optional arguments specified at the end of the function call.

The second argument, severity, can be used to enforce some level of control over the behavior of the exception, similar to what SQL Server uses error levels for. For the most part, the same exception ranges apply: exception levels between 1 and 10 result in a warning, levels between 11 and 18 are considered normal user errors, and those above 18 are considered serious and can only be raised by members of the sysadmin fixed server role. User exceptions raised over level 20, just like those raised by SQL Server, cause the connection to break. Beyond these ranges, there is no real control afforded to user-raised exceptions, and all are considered to be statement level—this is even true with XACT_ABORT set.

The state argument can be any value between 1 and 127, and has no effect on the behavior of the exception. It can be used to add additional coded information to be carried by the exception—but it's probably just as easy to add that data to the error message itself in most cases. I generally use a value of 1 for state when raising custom exceptions.

The simplest way to use RAISERROR is to pass in a string containing an error message, and set the appropriate error level. For general exceptions, I usually use 16:

RAISERROR('General exception', 16, 1)

This results in the following output:

Msg 50000, Level 16, State 1, Line 1
General exception.

Note that the error number used in this case is 50000, which is the generic user-defined error number that will be used whenever passing in a string for the first argument to RAISERROR.

Formatting Error Messages

When defining error messages, it is generally useful to format the text in some way. For example, think about how you might write code to work with a number of product IDs, dynamically retrieved, in a loop. You might have a local variable called @ProductId, which contains the current ID that the code is working with. You might wish to define a custom exception that should be thrown when a problem occurs—and it would probably be a good idea to return the current value of @ProductId along with the error message.

In this case, there are a couple of ways of sending back the data with the exception. The first is to dynamically build an error message string:

DECLARE @ProductId INT
SET @ProductId = 100

/* ... problem occurs ... */

DECLARE @ErrorMessage VARCHAR(200)
SET @ErrorMessage =
'Problem with ProductId ' + CONVERT(VARCHAR, @ProductId)

RAISERROR(@ErrorMessage, 16, 1)

Executing this batch results in the following output:

Msg 50000, Level 16, State 1, Line 10
Problem with ProductId 100.

While this works for this case, dynamically building up error messages is not the most elegant development practice. A step in a better direction is to make use of a format designator and to pass @ProductId as an optional parameter:

DECLARE @ProductId INT
SET @ProductId = 100

/* ... problem occurs ... */

RAISERROR('Problem with ProductId %i', 16, 1, @ProductId)

Executing this batch results in the same output as before, but requires quite a bit less code, and you don't have to worry about defining extra variables or building up messy conversion code. The %i embedded in the error message is a format designator that means "integer." The other most commonly used format designator is %s, for "string."

You can embed as many designators as necessary in an error message, and they will be substituted in the order in which optional arguments are appended:

DECLARE @ProductId1 INT
SET @ProductId1 = 100

DECLARE @ProductId2 INT
SET @ProductId2 = 200

DECLARE @ProductId3 INT
SET @ProductId3 = 300

/* ... problem occurs ... */

RAISERROR('Problem with ProductIds %i, %i, %i',
16, 1, @ProductId1, @ProductId2, @ProductId3)

This results in the following output:

Msg 50000, Level 16, State 1, Line 12
Problem with ProductIds 100, 200, 300.

NOTE

Readers familiar with C programming will notice that the format designators used by RAISERROR are the same as those used by the C language's printf function. For a complete list of the supported designators, see the "RAISERROR (Transact-SQL)" topic in SQL Server 2005 Books Online.

Creating Persistent Custom Error Messages

Formatting messages using format designators instead of building up strings dynamically is a step in the right direction, but it does not solve one final problem: what if you need to use the same error message in multiple places? You could simply use the same exact arguments to RAISERROR in each routine in which the exception is needed, but that might cause a maintenance headache if you ever needed to change the error message. In addition, each of the exceptions would only be able to use the default user-defined error number, 50000, making programming against these custom exceptions much more difficult.

Luckily, SQL Server takes care of these problems quite nicely, by providing a mechanism by which custom error messages can be added to sys.messages. Exceptions using these error messages can then be raised by using RAISERROR and passing in the error number as the first parameter.

To create a persistent custom error message, use the sp_addmessage stored procedure. This stored procedure allows the user to specify custom messages for message numbers over 50000. In addition to an error message, users can specify a default severity. Messages added using sp_addmessage are scoped at the server level, so if you have multiple applications hosted on the same server, be aware of whether they define custom messages and whether there is any overlap—you may need to set up a new instance of SQL Server for one or more of the applications in order to allow them to create their exceptions. When developing new applications that use custom messages, try to choose a random range in which to create your messages, in order to avoid overlaps with other applications in shared environments. Remember that you can use any number between 50000 and 2147483647, and you don't need to stay in the 50000 range.

Adding a custom message is as easy as calling sp_addmessage and defining a message number and the message text. The following T-SQL defines the message from the previous section as error message number 50005:

EXEC sp_addmessage
@msgnum = 50005,
@severity = 16,
@msgtext = 'Problem with ProductIds %i, %i, %i'

Once this T-SQL is executed, an exception can be raised using this error message, by calling RAISERROR with the correct error number:

RAISERROR(50005, 15, 1, 100, 200, 300)

This causes the following output to be sent back to the client:

Msg 50005, Level 15, State 1, Line 1
Problem with ProductIds 100, 200, 300.

Note that when calling RAISERROR in this case, severity 15 was specified, even though the error was defined with severity 16. This brings up an important point about severities of custom errors: whatever severity is specified in the call to RAISERROR will override the severity that was defined for the error. However, the default severity will be used if you pass a negative value for that argument to RAISERROR:

RAISERROR(50005, −1, 1, 100, 200, 300)

This produces the following output (notice that Level is now 16, as defined):

Msg 50005, Level 16, State 1, Line 1
Problem with ProductIds 100, 200, 300.

It is recommended that, unless you are overriding the severity for a specific reason, you always use −1 for the severity argument when raising a custom exception.

Changing the text of an exception once defined is also easy using sp_addmessage. To do so, pass the optional @Replace argument, setting its value to 'Replace', as in the following T-SQL:

EXEC sp_addmessage
@msgnum = 50005,
@severity = 16,
@msgtext = 'Problem with ProductId numbers %i, %i, %i',
@Replace = 'Replace'

NOTE

In addition to being able to add a message and set a severity, sp_addmessage supports localization of messages for different languages. The examples here do not show localization; instead, messages will be created for the user's default language. For details on localized messages, refer to SQL Server 2005 Books Online.

Logging User-Thrown Exceptions

Another useful feature of RAISERROR is the ability to log messages to SQL Server's error log. This can come in handy especially when working with automated code, such as T-SQL running in SQL Server Agent jobs. In order to log any exception, use the WITH LOG option of the RAISERROR function, as in the following T-SQL:

RAISERROR('This will be logged.', 16, 1) WITH LOG

Note that specific access rights are required to log an error. The user executing the RAISERROR function must either be a member of the sysadmin fixed server role or have ALTER TRACE permissions.

Monitoring Exception Events with Traces

Some application developers go too far in handling exceptions, and end up creating applications that hide problems by catching every exception that occurs and not reporting it. In such cases it can be extremely difficult to debug issues without knowing whether an exception is being thrown. Should you find yourself in this situation, you can use a Profiler trace to monitor for exceptions occurring in SQL Server.

In order to monitor for exceptions, start a trace and select the Exception and User Error Message events. For most exceptions with a severity greater than 10, both events will fire. The Exception event will contain all of the data associated with the exception except for the actual message. This includes the error number, severity, state, and line number. The User Error Message event will contain the formatted error message as it was sent to the client.

For warnings (messages with a severity of less than 11), only the User Error Message event will fire. You may also notice error 208 exceptions ("Object not found") without corresponding error message events. These exceptions are used internally by the SQL Server query optimizer during the scope-resolution phase of compilation and can be safely ignored.
Other  
 
Video
Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
REVIEW
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone