Name that SQL Server Error message

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, unless you’ve spent countless hours memorizing them, there are many more that we do not know by heart.

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 Bing may produce the information. Or maybe not?

But did you know that 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 and 2008 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 2008. 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

In SQL Server 2000, the equivalent is:

SELECT *
FROM master.dbo.sysmessages
WHERE error = 8115 AND msglangid = 103

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

• SQL Server 2008 Books Online
• Download SQL Server 2008 Books Online (May 2009)
• sys.messages (Transact-SQL)

Cheers!

Joe