0

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?

Pure.Krome
  • 6,508
  • 18
  • 73
  • 87

2 Answers2

5

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

gbn
  • 6,079
  • 1
  • 18
  • 21
0

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

squillman
  • 37,883
  • 12
  • 92
  • 146
  • 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? – Pure.Krome Aug 14 '09 at 03:33
  • Ah, yes. I'm with you now. gbn's got it in his answer. Alerts are the way to go for general purpose things. – squillman Aug 14 '09 at 04:42