May 2, 2016 at 9:32 am
Hi,
In Sql Server 2012, I have setup (via checkbox in each job) to email the operator when any of my jobs fail. I'm not happy with the summary data that's sent via email and would instead like to send the results of my own custom sql (where I will provide job step details). I know how to write the sql to get me those custom results - however what I don't know is this:
What stored procedure is Sql Server calling to send those summary emails? I would like to intercept that procedure call and instead point it to my custom procedure.
To say this differently, when a job fails in Sql Server, what internal stored procedure is fired? Or is there possibly a trigger set someplace to fire that email?
Thanks !
Paul
May 2, 2016 at 10:54 am
I am not sure that such functionality is publicly facing. I have never seen that exposed.
May 2, 2016 at 11:37 am
SQL Server Agent should be using Database Mail - which uses sp_send_dbmail. You don't need to intercept anything...instead of checking the box to notify the operators, you just create a new job step with your code.
In the Advanced properties for the job - on failure redirect to the failure job step. On success - quit the job or go to the next step depending on how your agent job is set up.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 2, 2016 at 11:40 am
Well, yeah, but that's a different approach than he described above. Thing is though, to make it work, you have to master tokens. If you use the above advice, read up on them:
https://msdn.microsoft.com/en-us/library/ms175575.aspx
That is the only way you can get information at run time about the involved objects into your email text properly.
May 3, 2016 at 10:01 am
Ok, thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply