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