January 30, 2009 at 4:23 am
Environment
SQL 2005
I have around 5 DB under one instance and I have writen the following select statement to bring back all the job histry from the SQL Agent jobs
SELECT jh.Server as ServerName, j.name as JobName,
jh.Step_Name,jh.Message,jh.Run_Date as LastRunDate
FROM sysjobhistory jh
join sysjobs j
on jh.job_id = j.job_id
What I need
Now I have this detail, I want an email sent to me every time the 'Message' column contains the word 'Failed'.
Can this be done?
January 30, 2009 at 5:04 am
Yes, it can.
Set up a scheduled job and take a look at sp_send_dbmail.
January 30, 2009 at 5:07 am
Also, why don't you also consider setting up the notifiications on the scheduled jobs to send an e-mail when the job fails rather that querying the tables to look for this?
January 30, 2009 at 5:36 am
Thanks for the info.
Basically what I really want is to create a new table with the information from msdb and the query I have posted, therefore I can search one central table if I need to for auditing in the future.
I know who to use sp_send_dbmail but how can I get it to send mail when it sees the work 'Failed' in the 'message' column?
January 30, 2009 at 5:49 am
You can add the query into the @query parameter.
However, if you are holding all failed jobs in this table, you will need to put in some data check filter. You have the last run time in the table, so if, for example, the job runs every hour, you would need to only check the table where LastRunTime >= DATEADD(hh, -1, GETDATE()) or something similar
January 30, 2009 at 6:43 am
Sorry im a bit lost!
Currently I use Database mail (sp_send_dbmail ), to execute an SP then email the results this run at 10am everyday.
exec msdb.dbo.sp_send_dbmail
@profile_name = 'EMail',
@recipients='example@examplemail.co.uk',
@subject ='Test email',
@query='exec TestV2.dbo.MytestSP'
GO
So what you are suggesting is to do the same thing but but design some SQL to be put in '@query'.
BUT
Does this not mean I would have to set this whole process up as a JOB?
I want it to be running all the time but email me when it gets a 'Failed job'
January 30, 2009 at 7:01 am
If you want it running all of the time, the obvious choice is a trigger against the sysjobhistory table - however, I'd not recommend you start putting triggers in the system tables...Not a good idea at all.
If you want to know when a job fails immediately, I would use the notification element of the scheduled job itself - thats what it is there for. I see little point in reinventing the wheel.
If you do want to keep a log of which jobs have failed, then there is no problem collecting these stats every day or you can increse the amount of history in the msdb database.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply