July 24, 2018 at 7:39 am
How do I do that? I should know this, but I'm not sure. Thank you for your reply.
July 24, 2018 at 7:56 am
Try this there was an extra /tr after the run date and time
'DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @s-2 VARCHAR(max)
SET @s-2 = '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>' +
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-2,
--@subject = 'Reconciliation SQL Scheduled Job Cancellations, Retries, or Failures for Today',
@body = @tableHTML,
@body_format = 'HTML' ;
July 24, 2018 at 8:27 am
Thanks. It is still returning empty email body unfortunately.
July 24, 2018 at 8:35 am
I wrapped isnull around the query results and it worked fine for me:
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @s-2 VARCHAR(max)
SET @s-2 = '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>' +
N'<th>Duration</th><th>Retries Attempted</th></tr>' +
CAST ( (
SELECT LEFT(isnull(j.name,''),40) AS td,'', isnull(jh.step_id,'') AS td,'', isnull(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,'',
isnull(STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':'),'') AS td,'',
isnull(jh.retries_attempted,0) 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>' ;
July 24, 2018 at 9:28 am
I'm not sure at this point what the issue could be.....still empty.
July 24, 2018 at 9:40 am
Set up a test job that will run this sql:
SELECT 1/0
It will fail. Then run the script again to see if the test failed job shows up in body of email.
July 24, 2018 at 9:50 am
Ok. I will try that now.
July 24, 2018 at 9:55 am
Ah, it worked! So, I'm assuming it is returning an empty email body because there isn't any failed jobs to report? If this is the case, how about the other statuses? Like 'Succeeded'?
Thanks so much.
July 24, 2018 at 10:51 am
EMtwo - Tuesday, July 24, 2018 9:55 AMAh, it worked! So, I'm assuming it is returning an empty email body because there isn't any failed jobs to report? If this is the case, how about the other statuses? Like 'Succeeded'?Thanks so much.
Yes. You can play around with this in the where clause to test it:
AND jh.run_status IN (0,2,3)
Sue
July 24, 2018 at 11:04 am
Thank you Sue.
July 24, 2018 at 1:10 pm
Chris Hurlbut - Tuesday, July 24, 2018 8:35 AMI wrapped isnull around the query results and it worked fine for me:DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @s-2 VARCHAR(max)
SET @s-2 = '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>' +
N'<th>Duration</th><th>Retries Attempted</th></tr>' +
CAST ( (
SELECT LEFT(isnull(j.name,''),40) AS td,'', isnull(jh.step_id,'') AS td,'', isnull(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,'',
isnull(STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':'),'') AS td,'',
isnull(jh.retries_attempted,0) 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>' ;
Chris:
I ran the initial script without the ISNULL function just to see where the issue really lies - after running the script without the function it returned a result in the email body. There just wasn't a failed job to report.
Thanks again for all of your help.
July 24, 2018 at 1:24 pm
One last note.
What you should be doing for notifications for jobs that are mission critical is to:
Create an Operator for the job(s) that you want notifications to go to. (Right click - New Operator)
Name it some intuitive (in the Name field)
put the list of emails separated by semi-colon's for the people you want to receive the notification (in the e-mail name field)
For the SQL Agent Job Properties (select Notifications)
On the Actions to perform when the job completes (select e-mail)
Choose the operator name you just set-up
Then select when the job fails.
Now whenever the job fails you will receive a notification.
July 24, 2018 at 1:29 pm
Thank you!
July 27, 2018 at 8:33 am
So far my HTML out script is working just fine...I wanted to create another job to populate a table with the result from my query and I was able to do that. However, I want to be able to drop the table and recreate it with appended data every day - I am creating the table using the SELECT INTO SQL statement . I tried to use DROP TABLE , but I realize that when the table is dropped it doesn't recreate it using the statement as I hoped. Any ideas, suggestions, recommendations? I appreciate it.
July 30, 2018 at 7:23 am
Please disregard my last request. I figured it out. Thank you all for all of your help.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply