January 24, 2008 at 9:02 am
Good afternoon-
Can anyone assist with a script to generate an email to a specific inbox if a stored procedure fails? I perform a daily server update via a stored procedure and would like notification if the procedure fails. Thanks for your help!
George
January 24, 2008 at 9:10 am
How do you run the stored procedure? You can set alerts if jobs fail and have those send emails.
January 24, 2008 at 9:15 am
The stored procedure runs everyday at 6:30 AM, I thought alerts were for specific error messages. I would not necessarily know the error message from the script. Can alerts be configured for any error type?
January 24, 2008 at 2:28 pm
Assuming your errors a mild enough to not stop the execution of the stored procedure, you can use the msdb.dbo.sp_send_dbmail stored procedure. Books online gives you all the arguments for it.
You can do some pretty powerful things with it, but it's limited in that if an error occurs in the stored procedure that is severe enough to kill it, you obviously won't get an e-mail.
Another thing you can do is set up alerts when the job fails. It will give you a generic failure message.
What I do to debug stored procedures that run in a job is put a bunch of print statements throughout the procedure to output status messages, where it is in the execution, etc. Then go into the job step, click on the advanced page, and set up an output file. All of those print statements will get put into that file.
The Redneck DBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply