SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

Kyle Neier

Kyle has been working with computers professionally since 1995 in both a development and administrative capacity. For over 10 of those years, SQL Server has been the focus of both a professional and personal passion. Kyle has worked in online retail, manufacturing, EMR software, and pharmaceutical industries. Kyle joined PTI in 2011 as a Senior Microsoft Consultant where he continues to chase his passion of automating DBA processes and doing everything humanly possible to prevent the 0200 page. Kyle serves as the Vice-President of the Indianapolis chapter of PASS (www.indypass.org) and served on the committee to bring SQL Saturday to Indianapolis. He shares his passion for SQL Server with others through speaking and mentoring at local and regional events.


Leave a comment on the original post [sqldbamusings.blogspot.com, opens in a new window]

Loading comments...