The Anatomy of an Error Message

Here is a typical error message you can get from SQL Server when working from Query Analyzer.
Server: Msg 547, Level 16, State 1, Procedure error_demo_sp, Line 2
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'fk7_acc_cur'.
The conflict occurred in database 'bos_sommar', table 'currencies', column 'curcode'.
The statement has been terminated.
Note: Under Tools->Options->Connections, I have checked Parse ODBC Message Prefixes.The error information that SQL Server passes to the client consists of several components, and the client is responsible for the final interpretation of the message. These are the components that SQL Server passes to the client.
Message number – each error message has a number. You can find most of the message numbers in the table sysmessages in the master database. (There some special numbers like 0 and 50000 that do not appear there.) In this example, the message number is 547. Since most interesting messages are errors, I will also use the term error number. Message numbers from 50001 and up are user-defined. Lower numbers are system defined.
Severity level – a number from 0 to 25. The stort story is that if the severity level is in the range 0-10, the message is informational or a warning, and not an error. Errors resulting from programming errors in your SQL code have a severity level in the range 11-16. Severity levels 17-25 indicate resource problems, hardware problems or internal problems in SQL Server, and if the severity is 20 or higher, the connection is terminated. For the long story. For system messages you can find the severity level in master..sysmessages, but for some messages SQL Server employs a different severity level than what's in sysmessages.
State – a value between 0 and 127. The meaning of this item is specific to the error message, but Microsoft has not documented these values, so this value is rarely of interest to you.
Procedure – in which stored procedure, trigger or user-defined function the error occurred. Blank if the error occurred in a plain batch of SQL statements (including dynamic SQL).
Line – Line number within the procedure/function/trigger/batch the error occurred. A line number of 0 indicates that the problem occurred when the procedure was invoked.
Message text – the actual text of the message that tells you what went wrong. You can find this text in master..sysmessages, or rather a template for it, with placeholders for names of databases, tables etc.
As I mentioned the client is responsible for the formatting of the error message, and for messages with a severity level with 10 or lower, most client programs print only the message text, but not severity level, procedure etc. In fact, we see an example of this above. The text The statement has been terminated is a message on its own, message 3621.
When you write your own client program, you can choose your own way to display error messages. You may be somewhat constrained by what your client library supplies to you. The full information is available with low-level interfaces such as DB-Library, ODBC or the OLE DB provider for SQL Server. On the other hand, in ADO you only have access to the error number and the text of the message.
There are two ways an error message can appear: 1) an SQL statement can result in an error (or a warning) 2) you emit it yourself with RAISERROR (or PRINT). Let's take a brief look at RAISERROR here. Here is sample statement:
RAISERROR('This is a test', 16, 1)
Here you supply the message text, the severity level and the state. The output is:
Server: Msg 50000, Level 16, State 1, Line 1
This is a test
Thus, SQL Server supplies the message number 50000, which is the error number you get when you supply a text string to RAISERROR. (There is no procedure name here, since I ran the statement directly from Query Analyzer.) Rather than a string, you could have a supplied a number of 50001 or greater, and SQL Server would have looked up that number in sysmessages to find the message text. You would have stored that message with the system procedure sp_addmessage. (If you just supply a random number, you will get an error message, saying that the message is missing.) Whichever method you use, the message can include placeholders, and you can provide values for these placeholders as parameters to RAISERROR, something I do not cover here. Please refer to Books Online for details.
As I mentioned State is rarely of interest. With RAISERROR, you can use it as you wish. If you raise the same message in several places, you can provide different values to State so that you can conclude which RAISERROR statement that fired. The command-line tools OSQL and ISQL have a special handling of state: if you use a state of 127, the two tools abort and set the DOS variable ERRORLEVEL to the message number. This can be handy in installation scripts if you want to abort the script if you detect some serious condition. (For instance, that database is not on the level that the installation script is written for.) This behaviour is entirely client-dependent; for instance, Query Analyzer does not react on state 127.

Comments

Post a Comment

Popular posts from this blog

Google to FTS Syntax Cheat Sheet

Image
OPERATOR EXAMPLE DESCRIPTION   nut Searches for inflectional forms of the word nut crank arm crank AND arm Searches for documents containing inflectional forms of the words crank AND arm crank and ann. The keyword AND is optional. tire OR air Searches for documents containing inflectional forms of the words tire or air, “reflector bracket” Performs a phrase search for the phrase "reflector bracket". hardware -bracket Searches for documents containing inflectional forms of the word hardware but not the word bracket. +clamp Searches for the word darn') without generating inflectional forms. ~seat Searches for thesaurus forms of the word seat Assemb* Searches for words that begin with the prefix assemb . <washer nut> Searches for documents that contain the words washer in close proximity to the word nut
Read more

@@rowcount

@@rowcount is a global variable reports the number of affected rows in the most recently executed statement. Just like @@error you need to save it in a local variable if you want to use the value later, since @@rowcount is set after each statement. Since with SET you can only assign variable at a time, you must use SELECT if you need to save both @@error and @@rowcount into local variables: SELECT @err = @@error, @rowc = @@rowcount (For this reason, I prefer to always use SELECT for variable assignment, despite Microsoft's recommendations to use SET.) In T-SQL it is not an error if, for instance, an UPDATE statement did not affect any rows. But it can of course indicate an error in your application, as it could be an error if a SELECT returns more that one row. For these situations, you can check @@rowcount and raise an error and set a return value, if @@rowcount is not the expected value.
Read more

Sql Index

Image
Indexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster.
Read more