Joe Webb Blog

Musing and observations about SQL Server, other technogies, and sometimes just life in general

SQL Server error messages

 

If you've worked with Microsoft SQL Server in a production environment for any length of time, you've undoubtedly been exposed to a number of different error messages. For example, many of us are probably familiar, too familiar perhaps, with error 1205 - the dreaded "you've been chosen as the victim of a deadlock" message.

Or maybe you are more of a developer than a DBA. So you may be more familiar with error messages like 2714 - "there is already an object named this in the database."

There may be many messages that we feel that we know by heart. However there are many more that we do not.

Have you ever been given an error number without the associated message? If someone came to you and said "The application gave me error 8115. What does that mean?" would you know where to look? A quick search on Google or Windows Live Search may produce the information you seek.

However, SQL Server has this information built into its metadata. The sysmessages system table in SQL Server 2000 and the sys.messages catalog view in SQL Server 2005 contain a list of errors that SQL Server may produce and their associated messages.

So, to quickly see the message test associated with error number 8115, you can run the following query in SQL Server 2005. I have filtered the output to only show results in English; other languages may be available.

SELECT         
*
FROM
sys.messages
WHERE
message_id = 8115 AND
language_id = 1033

The equivalent SQL Server 2000 equivalent is as follows.

SELECT         
*
FROM
master.dbo.sysmessages
WHERE

error = 8115 AND
msglangid = 1033

 

Once again, I have limited the output to English.

For more information, refer to Books Online and look up sys.messages for SQL Server 2005 or sysmessages for SQL Server 2000.

Cheers!

Joe

kick it on DotNetKicks.com

Legacy Comments


Shambhu A. Vishwakarma
2007-12-13
re: SQL Server error messages
It's really helpful :)

Praveenkumar Jeykumar
2008-11-12
re: SQL Server error messages
Really explained with a good and simple example.
Thank you very much

Mike Groh
2009-01-07
re: SQL Server error messages
Thanks Joe! Good information!

Praveen Agrawal
2009-07-28
re:SQL Server error messages
Its HelpFul, Nice Example

Hosein
2010-08-08
re: SQL Server error messages
Thanx a lot! it wa really helpful