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.