THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Another Webcast Thanks: Errors and Exceptions in SQL Server 2005

Thank you to everyone who attended today's webcast on Errors and Exceptions in SQL Server 2005!  There were a huge number of excellent questions and I had a really great time talking to you all about what I feel is a very important topic. The webcast is now available for offline viewing, for those who didn't catch it (for shame).

The contest for the free copy of "Expert SQL Server 2005" generated some interesting responses that I wasn't expecting--and I learned a bit about the error messages that I didn't know before!  For those who weren't there, I asked attendees to e-mail me the count of the number of exceptions in sys.messages that are at the highest level of severity.

When I asked, I expected the correct answer to be 9:

SELECT COUNT(DISTINCT message_id)
FROM sys.messages
WHERE
 severity =
 (
  SELECT MAX(severity)
  FROM sys.messages
 )

However, I received quite a range of answers, so I broadened the acceptable pool a bit to include any of the following:

A) 9, as covered above

B) 81, or 90, which would result from the following query run on RTM or SP2, respectively (I had no idea that the SP would change the answer here, and I was vague about whether language-specific messages should count):

SELECT COUNT(*)
FROM sys.messages
WHERE
 severity =
 (
  SELECT MAX(severity)
  FROM sys.messages
 )

C) 158 or 163, if the query from (A) is run on 2005 RTM or SP2 using severity >= 20, rather than the subquery.  I mentioned in the webcast that anything >= 20 is the highest category of severities in SQL Server, so that's a valid answer--it means you were listening!

D) 1422 or 1630, if the query from (B) is run on 2005 RTM or SP2 using severity >= 20.

E) 4, if you told me that you ran the query in SQL Server 2000.

F) ?  I'm still reading through a few more responses to figure out if I want to add another acceptable answer or two.

I will write to everyone who entered tomorrow and let you know if a copy is on the way.  Thanks again for attending.

The code samples used in the session are attached to this post.

 

Published Friday, May 18, 2007 4:02 PM by Adam Machanic

Attachment(s): exception_demos.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Adam Machanic said:

Today I gave two talks at New England Code Camp 8 . A fun experience as always, and for those of you

September 29, 2007 4:08 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement