1

I need to create a trigger on one of the sysjobxxx tables in MS SQL Server which when a record of a job is inserted, needs to be triggered. For eg. when jobA is executed, some of the sysjobxxx tables (sysjobhistory, sysjobs, sysjobsteps ) get records inserted. My trigger needs to be built on the table that inserts the success/failure info of jobA. I'd thought that sysjobhistory would be the one but when I tested with a dummy job that failed, it inserted 2 records, instead of 1 - this would run my trigger (upon insert) twice, instead of just once.

What I'm trying to accomplish is to get the full description of job failure, everytime a job fails and is inserted into the sysjobxxx tables. I know there's an email notification that can be sent out but the description of failure is truncated (to 1024 chars) and unless a full 'View history' is done on the job, it's not possible to see the complete job failure description.

Is there a way I can create a trigger on one of the sysjobxxx tables, that upon a job record insertion, checks a column (I don't know which one indicates failure of the job), then sends out an email (directly from within the trigger via sp_sendmail or calls another stored proc that then executes sp_sendmail) to a list of recipients, with the full job failure description?

Roger Dodger
  • 927
  • 2
  • 16
  • 37
  • please explain with examples,also look out here ,on to see why sending emails from triggers is a bad idea http://stackoverflow.com/a/10755518/2975396 – TheGameiswar Dec 13 '16 at 17:46
  • 1
    For each job step (or just the steps you care about) you can "Log to table", which captures descriptions that are > 1024 characters. Is that a viable option for you? – Dave Mason Dec 13 '16 at 18:09
  • @DMason - It's not the steps that I care about per se. All I care about is anytime a job fails, send out an email with full description of the error, irrespective of < or > 1024 chars. – Roger Dodger Dec 13 '16 at 18:14
  • @TheGameiswar - That article talks about potential pitfalls of having a sp_sendmail inside a trigger, but what about executing a stored proc from inside the trigger, which in turn sends out the email? – Roger Dodger Dec 13 '16 at 18:15
  • You could add a second job step to only execute in the event the first execution was successful. This could capture newly created items from your source table, and build the notification e-mail from a secondary stored procedure call. This has the added benefit of decoupling the two events, allowing you to disable e-mails when testing. – Michael G Dec 13 '16 at 18:26
  • @MichaelG - This is not an option for me - there are hundreds of already created jobs, which cannot be modified. – Roger Dodger Dec 13 '16 at 18:58
  • 1
    I would stay away from triggers on system tables. Have you looked at `msdb.dbo.sysjobhistory`? The `message` column should have the data you're looking for. – Dave Mason Dec 13 '16 at 19:00
  • @DMason yes I've looked at msdb.dbo.sysjobhistory (in my original qn), but I need a way to email the COMPLETE 'message' row in that table, to be emailed as soon as an error for a job is logged into that table. – Roger Dodger Dec 13 '16 at 19:04
  • Just to clarify, adding a job step to the existing jobs is out of the question? – Dave Mason Dec 13 '16 at 19:09
  • @DMason Yes - this is not for any ONE job or for existing jobs - I CANNOT modify them. I need a separate, standalone process that needs to capture the full, un-truncated error message everytime a job fails. – Roger Dodger Dec 13 '16 at 19:46
  • Ok, I'm pretty sure I understand what you want to do. But I don't fully understand why. I suspect the limitations you've shared can be overcome in ways other than a trigger. So I'd like to ask another question if you're up for it. (Let's set aside the issue of the incomplete error messages for now.) Why can't you modify the jobs? You could add yourself as an operator and E-mail, Page, or Net-Send to you when the job fails. – Dave Mason Dec 13 '16 at 21:14
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/130560/discussion-between-damon-matt-and-dmason). – Roger Dodger Dec 13 '16 at 22:59
  • @DMason - I have no permission to modify any jobs. I have to find an alternative without modifying existing jobs. – Roger Dodger Dec 13 '16 at 23:02
  • You have "no permission". Why not? Are we talking SQL permission? Is this a political thing? – Dave Mason Dec 14 '16 at 01:08
  • @DMason - Yes political/organization policy – Roger Dodger Dec 14 '16 at 14:28
  • 1
    A technical solution for a political problem, eh? IMO, that's not going to leave anyone satisfied. I'm sure many of us have been in your shoes and sympathize with you. I don't think there's anything more I can add, other than "Good luck!" – Dave Mason Dec 14 '16 at 14:58
  • 1
    @DMason Not a solution, but +3'd (points) for your effort. TY. – Roger Dodger Dec 14 '16 at 22:48
  • @Roger Dodger got the solution? – meekash55 Nov 07 '22 at 14:56

0 Answers0