CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Syntax Error
Syntax Error

Reputation: 1640

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 Programmability 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.

Thanks.

Upvotes: 2

Views: 2346

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28890

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 = '[email protected]',  
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder  
                  WHERE DueDate > ''2004-04-30''  
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,  
    @subject = 'stored procedures Failures'

If i want to send email,i will use above approach instead of sending email for every failure.Since i logged data into a table,i can run a SQL job every 1 hour or so depending on frequency i need

Upvotes: 2

steoleary
steoleary

Reputation: 9278

Yes, it's pretty simple, just use normal error handling such as TRY/CATCH and then in your catch block use sp_send_dbmail to send an email to whatever recipients you require.

Upvotes: 1

Related Questions