05 July 2007 07:45 tonyrogerson

Couple RAISERROR with Profiler / Event Notifications - see whats going on!

Leo Pasta gives a good tip on using RAISERROR and NOWAIT, in addition to this in SQL 2005 we can trap the 'User Error Message' EventClass both in SQL Profiler and using Event Notifications.

Here's how...

  1. Open Management Studio
  2. New Query and make a note of @@SPID
  3. Open SQL Profiler
  4. Select Blank trace template
  5. Select 'User Error Message' EventClass from Errors and Warnings.
  6. Filter on SPID = the @@SPID from (2) above
  7. Execute your RAISERROR..

    raiserror( 'hello there', 0, 1 ) with nowait

  8. That will now appear in Profiler!

 

Is that cool or what; I actually use this to good effect at a client, I've set it up using the Event Notifications, basically any error over a certain level I receive via email, so - when one of the developers (you know who you are Darren ;)) mispells an object name I know about it. It's really useful, think of the power of that!

Note, the User Error Message isn't limited to RAISERROR, it's not trapping that; it's trapping the message - so, when you get duplicate key messages etc... you'll get a message.

Good post Leo!

 

Filed under:

Comments

# Interesting Finds: July 5, 2007

05 July 2007 15:29 by Jason Haley

# re: Couple RAISERROR with Profiler / Event Notifications - see whats going on!

09 July 2007 11:48 by AndyC London

There is also sp_trace_generateevent which can be used to report diagnostic information.