Applies to:

MS SQL Server 7
MS SQL Server 2000
MS SQL Server 2005

How to understand error messages with MSSQL

First we need to generate a few errors.
Then we can analyze them.

Data used

SELECT CURRENT_TIMESTAMP AS 'Right now'

       { d '2007-08-01' } AS 'Date only', 

       { ts '2007-08-01 15:00:00' } AS 'Date & time',

       { t '19:21:33' } AS 'Time only'

go

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '{'.

SELECT * FROM salesxyz;

go

Msg 208, Level 16, State 1, Line 2
Invalid object name 'salesxyz'.

DELETE FROM sales

WHERE employees = 'ABB03';

go

Msg 207, Level 16, State 1, Line 2
Invalid column name 'employees'.

MSSQL Error analysis

The error/info/warning messages are passed to the client application.
It's the client application responsibility to format them and present them to the user.
All MSSQL error messages have the same structure:

Category	Explanation
Message number < 50000	Most system messages are stored in the MASTER database, table SYSMESSAGES by message number
Message number > 50000	Programmer defined messages stored in the MASTER database, table SYSMESSAGES by message number
Severity level	A number between 0 and 25
Severity level	A number between 0 and 10: It's either an information message or a warning message.
Severity level	A number between 11 and 16: It's a SQL programming error.
Severity level	A number between 17 and 25: It's a resource problems, hardware problems or internal problems in SQL Server.
State	A number between 0 and 127: Undocumented by Microsoft
Procedure	Name of the stored procedure, trigger or user-defined function that the error occurred.
Procedure	Blank if the error occurred in an interactive SQL statement.
Line	Line number within the procedure/function/trigger/batch the error occurred.
Line	Line number equal 0 means that the problem occurred when the procedure/function/trigger was invoked.
Message text	The actual text of the message. It should give you an indication of what is the problem.

@@error

@@error allows you to read what was the previous error message.

DELETE FROM sales

WHERE employees = 'ABB03';

go

Msg 207, Level 16, State 1, Line 1
Invalid column name 'employees'.

SELECT @@ERROR AS 'Last error was:'

go

Last error was:
---------------
207

(1 row(s) affected)

SELECT @@ERROR AS 'Error before was:'

go

Error before was:
-----------------
0

(1 row(s) affected)

Immediately after the error, you can read the error message.
After the next statement, @@error has been reset to 0.
If the error message has a severity of 11+, @@error will store it's value.
If the error message has a severity of 10-, @@error will NOT store it's value.

Custom error messages

You can create you own error messages with:

sp_addmessage

The customer error message, starts at: 50001

You can create error messages on the fly with:

RAISERROR('Test message #1', 16, 1);

go

Msg 50000, Level 16, State 1, Line 2
Test message #1

RAISERROR('Test message #4', 4, 3);

go

Test message #4
Msg 50000, Level 4, State 3

The severity decides the colour: red = error, black = warning/info.
Notice the error message # before the message.
Notice the info message # after the message.