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

Question

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?

Answer 1

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

Answer 2

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