20 February 2007

SQL Server Error Handling Workbench

Grant Fritchey steps into the workbench arena, with an example-fuelled examination of catching and gracefully handling errors in SQL 2000 and 2005, including worked examples of the new TRY..CATCH capabilities.

Error handling in SQL Server breaks down into two very distinct situations: you’re handling errors because you’re in SQL Server 2005 or you’re not handling errors because you’re in SQL Server 2000. What’s worse, not all errors in SQL Server, either version, can be handled. I’ll specify where these types of errors come up in each version.

The different types of error handling will be addressed in two different sections. ‘ll be using two different databases for the scripts as well, [pubs] for SQL Server 2000 and [AdventureWorks] for SQL Server 2005.

I’ve broken down the scripts and descriptions into sections. Here is a Table of Contents to allow you to quickly move to the piece of code you’re interested in. Each piece of code will lead with the server version on which it is being run. In this way you can find the section and the code you want quickly and easily.

As always, the intent is that you load this workbench into Query Analyser or Management Studio and try it out for yourself! The workbench script is available in the downloads at the bottom of the article.

SQL Server 2000 – GENERATING AN ERROR

SQL Server 2005 – GENERATING AN ERROR

SQL Server 2000 AND 2005 – ERROR SEVERITY AND EXCEPTION TYPE

The error message provides several pieces of information:

Msg
A message number identifies the type fo error. Can up to the value of 50000. From that point forward custom user defined error messages can be defined.
Level

The severity level of the error.

  • 10 and lower are informational.
  • 11-16 are errors in code or programming, like the error above.
  • Errors 17-25 are resource or hardware errors.
  • Any error with a severity of 20 or higher will terminate the connection (if not the server).
Line
Defines which line number the error occurred on and can come in extremely handy when troubleshooting large scripts or stored procedures.
Message Text
The informational message returned by SQL Server.

Error messages are defined and stored in the system table sysmessages.

SQL Server 2000 – CATCH AN ERROR

SQL Server 2000 does not allow us to stop this error being returned, but we can try to deal with it in some fashion. The core method for determining if a statement has an error in SQL Server 2000 is the @@ERROR value. When a statement completes, this value is set.

If the value equals zero(0), no error occured. Any other value was the result of an error.

The following TSQL will result in the statement ‘A constraint error has occurred’ being printed,as well as the error.

@@ERROR is reset by each and every statement as it occurs. This means that if we use the exact same code as above, but check the @@ERROR function a second time, it will be different.

You will see the error number as returned by the @@ERROR statement as being zero(0), despite the fact that we just had a clearly defined error.

The problem is, while the UPDATE statement did in fact error out, the IF statement executed flawlessly and @@ERROR is reset after each and every statement in SQL Server.

In order to catch and keep these errors, you need to capture the @@ERROR value after each execution.

Now we can capture the error number and refer to it as often as needed within the code.

SQL Server 2005 – CATCH AN ERROR

While @@ERROR is still available in SQL Server 2005, a new syntax has been added to the T-SQL language, as implemented by Microsoft: TRY... CATCH.

This allows us to finally begin to perform real error trapping.

While there is an error encountered in the code, none is returned to the calling function. In fact, all that will happen in this case is the string 'Error Handled' is returned to the client.

We have actually performed the function of error trapping within TSQL.

There are a number of issues around the use of TRY...CATCH that have to be dealt with, which we will cover later. For example, simply having a TRY...CATCH statement is not enough.

Consider this example:

The second error is handled, but the first one is not and we would see this error returned to client application:

To eliminate this problem place multiple statements within the TRY statement.

SQL Server 2000 – USING RAISERROR

The RAISERROR function is a mechanism for returning to calling applications errors with your own message. It can use system error messages or custom error messages. The basic syntax is easy:

To execute RAISERROR you’ll either generate a string, up to 400 characters long, for the message, or you’ll access a message by message id from the master.dbo.sysmessages table.

You also choose the severity of the error raised. Severity levels used in RAISERROR will behave exactly as if the engine itself had generated the error. This means that a SEVERITY of 20 or above will terminate the connection. The last number is an arbitrary value that has to be between 1 and 127.

You can format the message to use variables. This makes it more useful for communicating errors:

You can use a variety of different variables. You simply have to declare them by data type and remember that, even with variables, you have a 400 character limit. You also have some formatting options.

A few notes about severity and status. Status can be any number up to 127 and you can make use of it on your client apps. Setting the Status to 127 will cause ISQL and OSQL to return the error number to the operating environment.

SQL SERVER 2005 – USING RAISERROR

The function of RAISERROR in SQL Server 2005 is largely the same as for SQL 2000. However, instead of 400 characters, you have 2047. If you use 2048 or more, then 2044 are displayed along with an ellipsis.

RAISERROR will cause the code to jump from the TRY to the CATCH block.

Because of the new error handling capabilities, RAISERROR can be called in a more efficient manner in SQL Server 2005. This from the Books Online:

SQL Server 2000 – RETURNING ERROR CODES FROM STORED PROCEDURES

Stored procedures, by default, return the success of execution as either zero or a number representing the failure of execution, but not necessarily the error number encountered.

This will cause an error and the SELECT statement will return a non-zero value. On my machine, -6. In order take control of this, modify the procedure as follows:

This time the SELECT @err statement will return the 547 error number in the results. With that, you can begin to create a more appropriate error handling routine that will evolve into a coding best practice within your organization.

SQL Server 2005 – RETURNING ERROR CODES FROM STORED PROCEDURES

In order to appropriately handle errors you to know what they are. You may also want to return the errors to the calling application. A number of new functions have been created so that you can appropriately deal with different errors, and log, report, anything you need, the errors that were generated.

When you run the code above, you should receive this on the client, in the message, with a non-zero number in the result set:

In other words, everything you need to actually deal with errors as they occur.

You’ll also notice that the procedure returned an error value (non-zero) even though we didn’t specify a return code. You can still specify a return value as before if you don’t want to leave it up to the engine.

SQL Server 2000 – TRANSACTIONS AND ERROR TRAPPING

The one area of control we do have in SQL Server 2000 is around the transaction. In SQL Server 2000 you can decide to rollback or not, those are your only options. You need to make decision regarding whether or not to use XACT_ABORT. Setting it to ON will cause an entire transaction to terminate and rollback in the event of any runtime error. if you set it to OFF, then in some cases you can rollback the individual statement within the transaction as opposed to the entire transaction.

Modify the procedure to handle transactions:

Since the above code will generate an error on the second statement, the transaction is rolled back as a unit. Switch to the results in order to see that the zip code is, in fact, still 90210. If we wanted to control each update as a seperate statement, in order to get one of them to complete, we could encapsulate each statement in a transaction:

In this case then, the return value will be ‘90210’ since the first update statement will complete successfully. Be sure that whatever mechanism you use to call procedures does not itself begin a transaction as part of the call or the error generated will result in a rollback, regardless of the commit within the procedure. In the next example, we’ll create a transaction that wraps the other two transactions, much as a calling program would. If we then check for errors and commit or rollback based on the general error state, it’s as if the inner transaction that was successful never happened, as the outer transaction rollback undoes all the work within it.

SQL Server 2005 – TRANSACTIONS AND ERROR TRAPPING

The new error handling changes how transactions are dealt with. You can now check the transaction state using XACT_STATE() function. Transactions can be:

  • Closed (equal to zero (0))
  • Open but unable to commit (-1)
  • Open and able to be committed (1)

From there, you can make a decision as to whether or not a transaction is committed or rolled back. XACT_ABORT works the same way.

SQL Server 2005 – EXTENDED 2005 ERROR TRAPPING

With the new TRY...CATCH construct, it’s finally possible to do things about errors, other than just return them. Take for example the dreaded deadlock. Prior to SQL Server 2005, the best you could hope for was to walk through the error messages stored in the log recorded by setting TRACEFLAG values. Now, instead, you can set up a retry mechanism to attempt the query more than once.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

Downloads

This post has been viewed 123378 times – thanks for reading.

  • Rate
    [Total: 171    Average: 4.2/5]
  • Share

Grant Fritchey

View all articles by Grant Fritchey

Related articles

Also in BI

Relational Algebra and its implications for NoSQL databases

With the rise of NoSQL databases that are exploiting aspects of SQL for querying, and are embracing full transactionality, is there a danger of the data-document model's hierarchical nature causing a fundamental conflict with relational theory? We asked our relational expert, Hugh Bin-Haad to expound a difficult area for database theorists.… Read more

Also in SQL

SQL Server System Functions: The Basics

Every SQL Server Database programmer needs to be familiar with the System Functions. These range from the sublime (such as @@rowcount or @@identity) to the ridiculous (IsNumeric()) Robert Sheldon provides an overview of the most commonly used of them.… Read more

Also in SQL Server

The SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store

The SQL Server 2016 Query Store can give you valuable performance insights by providing several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. However, it can also directly affect the performance of queries by forcing Execution Plans for specific queries.… Read more

Also in T-SQL Programming

SQL Server Metadata Functions: The Basics

To be able to make full use of the system catalog to find out more about a database, you need to be familiar with the metadata functions. They save a great deal of time and typing when querying the metadata. Once you get the hang of these functions, the system catalog suddenly seems simple to use, as Robert Sheldon demonstrates in this article.… Read more
  • Anonymous

    related articles
    These are excellent articles on dealing with errors in sql server:

    http://www.sommarskog.se/error-handling-II.html
    http://www.sommarskog.se/error-handling-I.html

  • Anonymous

    SQL Server Error Handling Workbench
    Very detailed!

  • Anonymous

    SQL Server Error Handling Workbench
    Great article!

  • Anonymous

    SQL Server Error Handling Workbench
    Very detailed in SQL Server 2005 error handling technique.

  • Anonymous

    Article reader
    Nicely described..Thanks.

  • Anonymous

    SQL Server Error Handling Workbench
    This Error Handling Work Bench is great. I have read a lot of information in relations to error trapping but I have not see much on how to trap the actual statement that causes the error. In case of Dynamic SQL how can one go about catching or trapping the statement?

  • Anonymous

    Dynamic SQL
    You know, I’m not sure. I’ll get back to you on that one.

  • Patrick Index

    Thanks
    Very clear and well written.

  • Anonymous

    SQL Server Error Handling Workbench
    Great article!

  • Bill

    Bill,Best Post
    MSN I NIIPET
    <a href=”http://msn.com”>MSN</a>

  • Anonymous

    Just a little more help needed…
    This is all good information but my problem has to do with a “severe” error that I can’t seem to trap and evaluate in SQL Server 2000 (and no, I can’t switch to 2005, this is on our customers’ machines). And I’ve not been able to find info on this specific problem, even when I find informative articles such as yours.

    This is essentially the statement I’d like to catch and gracefully quit if it occurs:

    CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
    ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
    SET @ErrorNumber = @@ERROR

    Execution never gets to the “SET @ErrorNumber = @@ERROR” statement so I can’t act on it. In stead it bombs right away and gives me this error message:

    Server: Msg 1505, Level 16, State 1, Line 1
    CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 5. Most significant primary key is ‘706’.
    The statement has been terminated.

    When that CREATE statement is executed I’d like to gracefully exit the stored procedure (sproc) and report the error to the operator. It’s not that I don’t understand the error – I fully expect it with SOME of our customers – the problem is that I want to report the REASON for the error to our customers of various expertise.

    I created a series of sprocs to re-create indexes in our customers’ databases when we define them. Indexes were not defined before, or were defined with random names, so now I’m trying to manage index names and designs explicitly with a series of sprocs I create in SQL Server 2000 to search for, and/or delete, and/or recreate if necessary after verifying the suitability of the index names and syntax as defined by our developers.

    Now, above I said I was “essentially” executing that CREATE statement – to be more specific, I’ve tested all of these statements below and none of them are allowing me to trap: (Note, @SQLString is the CREATE statement above)

    EXEC @ErrorNumber = sp_executesql @SQLString
    PRINT @ErrorNumber, @@ERROR –<– This print statement does nothing since it errors out.

    EXEC ( @SQLString )
    SET @ErrorNumber = @@ERROR –<– This set statement does nothing since it errors out.

    CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
    ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
    SET @ErrorNumber = @@ERROR –<– This set statement does nothing since it errors out.

    If you need more info, I can expand.

  • Anonymous

    Just a little more help needed…
    This is all good information but my problem has to do with a “severe” error that I can’t seem to trap and evaluate in SQL Server 2000 (and no, I can’t switch to 2005, this is on our customers’ machines). And I’ve not been able to find info on this specific problem, even when I find informative articles such as yours.

    This is essentially the statement I’d like to catch and gracefully quit if it occurs:

    CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
    ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
    SET @ErrorNumber = @@ERROR

    Execution never gets to the “SET @ErrorNumber = @@ERROR” statement so I can’t act on it. In stead it bombs right away and gives me this error message:

    Server: Msg 1505, Level 16, State 1, Line 1
    CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 5. Most significant primary key is ‘706’.
    The statement has been terminated.

    When that CREATE statement is executed I’d like to gracefully exit the stored procedure (sproc) and report the error to the operator. It’s not that I don’t understand the error – I fully expect it with SOME of our customers – the problem is that I want to report the REASON for the error to our customers of various expertise.

    I created a series of sprocs to re-create indexes in our customers’ databases when we define them. Indexes were not defined before, or were defined with random names, so now I’m trying to manage index names and designs explicitly with a series of sprocs I create in SQL Server 2000 to search for, and/or delete, and/or recreate if necessary after verifying the suitability of the index names and syntax as defined by our developers.

    Now, above I said I was “essentially” executing that CREATE statement – to be more specific, I’ve tested all of these statements below and none of them are allowing me to trap: (Note, @SQLString is the CREATE statement above)

    EXEC @ErrorNumber = sp_executesql @SQLString
    PRINT @ErrorNumber, @@ERROR –<– This print statement does nothing since it errors out.

    EXEC ( @SQLString )
    SET @ErrorNumber = @@ERROR –<– This set statement does nothing since it errors out.

    CREATE UNIQUE NONCLUSTERED INDEX UQ_First_Key_SecondField_ThirdField
    ON [dbo].[DetailTable] ( Prime_Key, SecondField, ThirdField ) ON [PRIMARY]
    SET @ErrorNumber = @@ERROR –<– This set statement does nothing since it errors out.

    If you need more info, I can expand.

  • Granted

    Bad News
    That’s the kind of error you just can’t trap in SQL 2000. There are no options that I’m aware of. I’m sorry.

  • Anonymous

    Thanks
    This helped me lot. Great

  • Anonymous

    Error handling.
    Your article … workbench was informative concise and right on the mark. It answered a ton of questions for a SQL beginer like me. I certainly appreciated your effort, and knowledge base.

    If I may impose on your good nature and ask just 2 questions.

    1. What are the caveats that I should be aware of when adding user-defined error messages to the master.dbo.sysmessages tables. Can I create a private sysmessages table so that I can restore the original w/o restoring my master db.

    2. Is this a legitimate way to validate compliance with my application’s process logic. I relaize that I can use constraints, foreign keys etc to ensure data accuracy, but what my app needs is some way to quickly establish referential integrity accross processes. EG the rsults of Process B are consistent with the results of Process A,i.e. they either confirm or invalidate each other.

    Thanks again.

  • Anonymous

    How to handle the error in the first sight
    Really is very good. As a SQl DBA or A Developer he will learn a lot of things from this script.
    Really it is very nice.

    Thanks

  • Granted

    Re: Error Handling
    1. You’ve pretty much hit the caveat. You have to maintain them over time. It’s worse when you have multiple development teams working on different databases but all deploying to a single server. We have a ‘dba’ database that we put on all our servers. It has all kinds of maintenance routines & such, but it also includes a table to maintain this data.
    2.If the different apps are calling different databases… nope. If they’re calling the same database, you have to work with the constraint methods provided. You can add triggers, although you need to be careful with those.

  • Granted

    re: How to handl the error in the first sight
    I’m glad it was helpful.

  • Anonymous

    Help
    Thank you for this article. Very Informative. I’ve read thru it and some other articles on error trapping but i can’t seem to find a solution to my problem.

    I have a software (done in VB 6.0) connected to an SQL server 2003. User logs in, and the information is stored in a table (username, password, time log in, status, etc). My problem is the client-server connection is disconnected several times in a day. If this happens, the user currently using the system cant log in again because his login status is still TRUE. So, they need to call the admin user several times a day just to reset the login status of the user. My question is, is there a way I can catch that disconnection error so i can reset my login status to FALSE before i am disconnected from the SQL server? I tried using commit-rollback but to no avail. Or maybe i just dont know how to use it :(.

    Thanks for your help.

  • Anonymous

    Help
    Thank you for this article. Very Informative. I’ve read thru it and some other articles on error trapping but i can’t seem to find a solution to my problem.

    I have a software (done in VB 6.0) connected to an SQL server 2003. User logs in, and the information is stored in a table (username, password, time log in, status, etc). My problem is the client-server connection is disconnected several times in a day. If this happens, the user currently using the system cant log in again because his login status is still TRUE. So, they need to call the admin user several times a day just to reset the login status of the user. My question is, is there a way I can catch that disconnection error so i can reset my login status to FALSE before i am disconnected from the SQL server? I tried using commit-rollback but to no avail. Or maybe i just dont know how to use it :(.

    Thanks for your help.

  • Granted

    RE: Help
    You don’t really want to try to maintain connection information within the database in that manner because, as you see, the users can simply disconnect and there’s nothing you can do about it.

    Either just stop recording that, or, when the users log in, if the update statement that sets the record to true hits an error, catch it there.

    Sorry that wasn’t very helpful.

  • Anonymous

    Insert..
    Can I use this in Insert Statement? and what does it looks like?…

    Can someone use Error Handling for check constraint of au_id in pubs.dbo.authors?

  • Granted

    Insert..
    You’ve got two questions there, but I don’t see what you mean on either one. A little more clarification please?

  • Ron

    Great article, some help?
    I like your article and found it useful. Where I’m continuing to struggle though is for errors in Sql 2000 involving bad data from a file feed – for example, an invalid character in an integer only field. I’d like to catch this myself so that I can flag the record number in the file that caused the problem. Am I out of luck?

    Thx,

    Ron

  • Granted

    re: File Feeds
    Yep.

    Unfortunately 2000 just doesn’t have anything approaching the sophistication that we’re finally getting with 2005/2008. I’m sorry.

  • espasojevic

    Code doesn’t work as explained above
    Hi,

    I copied and pasted code above, but at the part:
    “Since the above code will generate an error on the second statement,
    the transaction is rolled back as a unit. Switch to the results in
    order to see that the zip code is, in fact, still 90210.”,
    it doesn’t work as expected, no matter if the option XACT_ABORT is turned on or not. As a matter of fact, first transaction got rolled back as well, so the value is 20853!
    Any idea?
    Thanks

  • espasojevic

    Code doesn’t work as explained above
    Hi,

    I copied and pasted code above, but at the part:
    “Since the above code will generate an error on the second statement,
    the transaction is rolled back as a unit. Switch to the results in
    order to see that the zip code is, in fact, still 90210.”,
    it doesn’t work as expected, no matter if the option XACT_ABORT is turned on or not. As a matter of fact, first transaction got rolled back as well, so the value is 20853!
    Any idea?
    Thanks

  • espasojevic

    Code doesn’t work as explained above
    Hi,

    I copied and pasted code above, but at the part:
    “Since the above code will generate an error on the second statement,
    the transaction is rolled back as a unit. Switch to the results in
    order to see that the zip code is, in fact, still 90210.”,
    it doesn’t work as expected, no matter if the option XACT_ABORT is turned on or not. As a matter of fact, first transaction got rolled back as well, so the value is 20853!
    Any idea?
    Thanks

Join Simple Talk

Join over 200,000 Microsoft professionals, and get full, free access to technical articles, our twice-monthly Simple Talk newsletter, and free SQL tools.

Sign up

See what's happening behind the scenes

Take a peek at the bowels of the ship – the lower decks – the actual servers of SQL Server Central itself.

See what's happening