Sp_altermessage “Classic”, much better than “New” sp_altermessage

I was reading through the SQL Server 2005 SP3 release notes yesterday (sadly, it was enjoyable) and clicked through to the Bugs Fixed link. After reading through the KB articles that I felt had direct implications to my current environment, I kept scrolling.

Third fix from the bottom is one of the more useful functions of SQL 2000 that was removed in SQL 2005:

“The sp_altermessage stored procedure can now be used to enable or disable system messages to the Windows event log in SQL Server 2005”

My mental radio immediately switched to Handel’s Hallelujah Chorus.

I remember my first time trying to send 229 errors to the event log on a SQL 2005 box. I had used this method extensively in the past to ferret out both legitimate and illegitimate permissions errors. When I ran the near instinctual statement:

sp_altermessage 229, with_log, true

I was greeted with an error message:

Msg 15178, Level 16, State 1, Procedure sp_altermessage, Line 20
Cannot drop or alter a message with an ID less than 50,000.

Much to my chagrin, I soon realized this wonderful tool had been stripped from my toolbox.

Evidently, in SP3, this was changed. Being the over-analytical paranoid skeptic that I am, I installed SP3 on my local Developer instance. I ran the command and received the three words every DBA loves to see:

Command(s) completed successfully.

So, for any of you who don’t think that the Connect website works, I’m pretty certain that Microsoft would have left this alone had it not been for the DBA warriors who kept this issue bubbling to the top.

As a side note, please forgive the pun regarding my least favorite soda drink.