Jay F1 Jay F1 - 1 year ago 65
SQL Question

Alert when stored procedure fails

Is it possible to have

SQL Server (2008)
send an email alert if a
stored procedure
fails for any reason?

I can do it quite easily from a SQL job but can't see any options within the
area or the properties of the
stored procedure
itself. This
stored procedure
is triggered via another application on demand. I have looked at doing it from the other application but this adds many layers of complication-I was hoping
SQL Server
had me covered on this!?

I have searched but not found anyone with the same question.


Answer Source

High level overview of how you can do this..

1.Modify stored Procs to Return 1 in case of failure and log into table.This also has some weakness because some failures wont be Caught
2.Once the information is logged into table,then sending email is easy using SP_SEND_DBMAIL.

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'Adventure Works Administrator',  
    @recipients = 'yourfriend@Adventure-Works.com',  
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder  
                  WHERE DueDate > ''2004-04-30''  
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,  
    @subject = 'stored procedures Failures'