SQL Sever Scheduled Jobs
-
Hi,
I'm trying to set up a scheduled job that runs daily and sends out an email to me when a job cancels, retries or fails and I would want to come out in this format(See attached image):
Any pointers or scripts you may have that can help me accomplish this will be helpful. -
You should be able to get all of this information from the table msdb.dbo.sysjobhistory
https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql?view=sql-server-2017&viewFallbackFrom=sql-server-2005A query for this might look something like:
SELECT LEFT(j.name,40) AS job_name, jh.step_id, LEFT(jh.step_name,40) AS step_name, jh.sql_message_id, jh.message,
CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In Progress'
END AS run_status,
msdb.dbo.agent_datetime(jh.run_date,jh.run_time) AS run_datetime,
STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS run_duration_formatted,
jh.retries_attempted
FROM msdb.dbo.sysjobhistory AS jh
INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = jh.job_id
WHERE jh.run_date >= CONVERT(int, CONVERT(varchar(8),GETDATE()-7,112))
AND jh.step_id > 0
AND jh.run_status IN (0,2,3)
ORDER BY jh.run_date, jh.run_time; -
Thanks. So I found this script to get the result of the script in a layout like the image attached. However, I get a blank result shown after I run the script instead of the result in the attached layout.
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @S VARCHAR(max)
SET @S = 'Reconciliation SQL Scheduled Job Cancellations, Retries, or Failures for '+CONVERT(VARCHAR(12),GETDATE(),107)SET @tableHTML =
N'<H1>Reconciliation SQL Scheduled Job Status</H1>' +
N'<table border="1">' +
N'<tr><th>Step Number</th>' +
N'<th>Message</th>' +
N'<th>Run Status</th><th>Run Date and Time</th></tr>' +
N'<th>Duration</th><th>Retries Attempted</th></tr>' +
CAST ( (select td = StepName, '',
td = message, '',
td = RunStatusDesc, '',
td = RunDateTime, '',
td = RunDuration, '',
td = retries_attempted, ''
from reporting..sqljobstatus
WHERE CONVERT(DATETIME, RTRIM(rundatetime)) = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) --- 1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients='john.doe@email.com',
--@recipients='john.doe@email.com; john.doe@email.com',
@subject = @S,
--@subject = 'Reconciliation SQL Scheduled Job Cancellations, Retries, or Failures for Today',
@body = @tableHTML,
@body_format = 'HTML' ; -
I'm not familiar with what reporting..sqljobstatus is. To adapt my query for use in HTML output, try:
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @S VARCHAR(max)
SET @S = 'Reconciliation SQL Scheduled Job Cancellations, Retries, or Failures for '+CONVERT(VARCHAR(12),GETDATE(),107)SET @tableHTML =
N'<H1>Reconciliation SQL Scheduled Job Status</H1>' +
N'<table border="1">' +
N'<tr><th>Job Name</th>'+
N'<th>Step Number</th>' +
N'<th>Message</th>' +
N'<th>Run Status</th><th>Run Date and Time</th></tr>' +
N'<th>Duration</th><th>Retries Attempted</th></tr>' +
CAST ( (
SELECT LEFT(j.name,40) AS td,'', jh.step_id AS td,'', jh.message AS td,'',
CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In Progress'
END AS td,'',
msdb.dbo.agent_datetime(jh.run_date,jh.run_time) AS td,'',
STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS td,'',
jh.retries_attempted AS td
FROM msdb.dbo.sysjobhistory AS jh
INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = jh.job_id
WHERE jh.run_date >= CONVERT(int, CONVERT(varchar(8), DATEADD(day,DATEDIFF(day,0,GETDATE()),0),112))
AND jh.step_id > 0
AND jh.run_status IN (0,2,3)
ORDER BY jh.run_date, jh.run_time
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ; -
Thanks! This is great....it works. I will adapt your query.
-
Is it possible to both the send the result of the query in an email as well as load a table with the same result simultaneously? Do you have a script that can do this if it is at all possible? Thanks so much for all of your help.
-
Unfortunately with the way you have to manipulate the query to make it produce HTML table formatting, you would have to use the original query I gave you and store the results in a temp table to do your INSERT, then query the temp table to produce the HTML table formatting. There really isn't a way to do it without 2 steps.
-
Ah, thanks!
-
So, I adapted your HTML output script and it appeared to be working perfectly fine - now the output sent by email is a blank page with no content just the header...I'm not sure what I did differently to cause it to stop working. Have you encountered this before?
-
No, I haven't seen this, I'd have to see the code you used to better understand the situation.
-
The only thing edited out in this script is the email address..See below:
'DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @S VARCHAR(max)
SET @S = 'Reconciliation SQL Scheduled Job Cancellations, Retries, or Failures for '+CONVERT(VARCHAR(12),GETDATE(),107)SET @tableHTML =
N'<H1>Reconciliation SQL Scheduled Job Status</H1>' +
N'<table border="1">' +
N'<tr><th>Job Name</th>'+
N'<th>Step Number</th>' +
N'<th>Message</th>' +
N'<th>Run Status</th><th>Run Date and Time</th></tr>' +
N'<th>Duration</th><th>Retries Attempted</th></tr>' +
CAST ( (
SELECT LEFT(j.name,40) AS td,'', jh.step_id AS td,'', jh.message AS td,'',
CASE jh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In Progress'
END AS td,'',
msdb.dbo.agent_datetime(jh.run_date,jh.run_time) AS td,'',
STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS td,'',
jh.retries_attempted AS td
FROM msdb.dbo.sysjobhistory AS jh
INNER JOIN msdb.dbo.sysjobs AS j ON j.job_id = jh.job_id
WHERE jh.run_date >= CONVERT(int, CONVERT(varchar(8), DATEADD(day,DATEDIFF(day,0,GETDATE()),0),112))
AND jh.step_id > 0
AND jh.run_status IN (0,2,3)
ORDER BY jh.run_date, jh.run_time
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Mail',
@recipients='johndoe@email.com',
--@recipients='johndoe@email.com; johndoe@email.com',
@subject = @S,
--@subject = 'Reconciliation SQL Scheduled Job Cancellations, Retries, or Failures for Today',
@body = @tableHTML,
@body_format = 'HTML' ; -
I'm not able to replicate the problem you describe with this code. I just changed the @profile_name and @recipients parameters and I get an e-mail sent from my server.
-
Hmm...I do get an email except it is blank, empty - when it worked, it did return the jobs that failed, retried, and or canceled... Unfortunately when I run it now what I receive is a blank email with the header only. I am going to look at the script and try it again to see if I find anything. Thank you very much, I appreciate your help.
-
OK, I hope your able to figure it out, the e-mail I get does have the listing of job steps that I expected from my job history, so I'm not sure why your e-mail sends out blank.
-
I would trap for nulls they could potentially be the problem as they don't concatenate very well.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply