Can Sql Server send an email alert if a RAISEERROR has be raised?

Hi folks,

is it possible to get the Sql Server 2008 to send an email when a query RAISEERROR is thrown .. or at the very least, when a RAISEERROR with a predetermined error code or codes?

From serverfault Pure.Krome

• Yes, you can put a try/catch block in and send an email using database mail. It'd go something like this:

  BEGIN TRY
     [...statement(s)...]
  END TRY
  BEGIN CATCH
     EXEC sp_send_dbmail @profile_name='MyProfile',
        @recipients='recipient@mydomain.com',
        @subject='Query Error',
        @body='An error occurred during execution of a statement.'
  END CATCH
  

  You can use @@Error in the CATCH block to detect the specific error level that was thrown.

  Here's more on the TRY/CATCH statement
  Here's more on SQL Server 2008 Database Mail

  Pure.Krome : Cheers for the answer :) What about an auto way, without having to manually putting that code in each stored proc/query ... like .. some autotrigger event thing?

  squillman : Ah, yes. I'm with you now. gbn's got it in his answer. Alerts are the way to go for general purpose things.
  From squillman

• You associate an SQL Agent "alert" to detect the error, which then sends an email to an "operator" or runs a job.

  You use sp_add_alert but the main info is here: Monitoring and Responding to Events

  Pure.Krome : cheers :) this is more what i'm after. thanks!
  From gbn