June 19, 2017 at 3:23 pm
I send myself a test email and it works fine (I receive it) but when I execute this piece of code, and it is supposed to email me if there are failed jobs, I get Mail queued error. What can be the reason for this?declare @FailedJobs Table (
job_name VARCHAR(255)
, run_date VARCHAR(20)
, run_time VARCHAR(20)
)
DECLARE @ServerName VARCHAR(255)
SET @ServerName = CONVERT(VARCHAR(255),SERVERPROPERTY('ServerName'))
declare @job_Name varchar (100)
select @job_Name = name from msdb.dbo.sysjobs
;WITH LastErrorMessage AS (
SELECT rn = row_number() OVER (PARTITION BY job_id ORDER BY instance_id DESC), job_id, step_id, message
FROM msdb.dbo.sysjobhistory WHERE step_id > 0
)
INSERT INTO @FailedJobs
SELECT j.[name]
, LEFT(CONVERT(VARCHAR(50), h.run_date),10)
, (CASE LEN(h.run_time)
WHEN 1 THEN '00:00:0' + CONVERT(CHAR(1),h.run_time)
WHEN 2 THEN '00:00:' + CONVERT(CHAR(2),h.run_time)
WHEN 3 THEN '00:0' + CONVERT(CHAR(1),LEFT(h.run_time,1)) + ':' + CONVERT(CHAR(2),RIGHT(h.run_time,2))
WHEN 4 THEN '00:' + CONVERT(CHAR(2),LEFT(h.run_time,2)) + ':' + CONVERT(CHAR(2),RIGHT(h.run_time,2))
WHEN 5 THEN '0' + CONVERT(CHAR(1),LEFT(h.run_time,1)) + ':' + LEFT(RIGHT(h.run_time,4),2) + ':' + CONVERT(CHAR(2),RIGHT(h.run_time,2))
ELSE
CONVERT(VARCHAR(4),LEFT(h.run_time,LEN(h.run_time)-4)) + ':' + LEFT(RIGHT(h.run_time,4),2) + ':' + CONVERT(CHAR(2),RIGHT(h.run_time,2))
END)
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j ON h.job_id = j.job_id
LEFT JOIN LastErrorMessage LEM ON j.job_id = LEM.job_id AND LEM.RN = 1
WHERE j.enabled = 1
AND j.name <> 'syspolicy_purge_history'
AND h.instance_id IN (
SELECT MAX(h.instance_id)
FROM msdb.dbo.sysjobhistory h
GROUP BY (h.job_id)
)
AND h.run_status = 0
AND msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(HOUR,-24, GETDATE())
IF EXISTS (SELECT TOP 1 job_name FROM @FailedJobs)
BEGIN
DECLARE report_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT job_name FROM @FailedJobs
OPEN report_cursor
FETCH NEXT FROM report_cursor INTO @Job_Name
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM report_cursor INTO @Job_Name
END
CLOSE report_cursor
DEALLOCATE report_cursor
DECLARE @body1 VARCHAR(MAX)
DECLARE @subject1 VARCHAR(64)
DECLARE @css VARCHAR(MAX)
SET @css = '
<style type="text/css">
#body {
font-family: verdana,arial,sans-serif;
font-size: 12px;
background: #FFF;
width: auto;
height: 525px;
margin: auto;
position: relative;
overflow: auto;
}
p {
font-family: verdana,arial,sans-serif;
padding: 5px 0px 0px;
}
.gray {
font-weight: 600;
color: #9A8B7D;
}
.green {
font-weight: 600;
color: #4BAA42;
}
.results {
font-family: verdana,arial,sans-serif;
border-collapse: collapse;
width: 100%;
margin: auto;
}
.resultsTitle {
font-family: Verdana,Arial,sans-serif;
background: #4BAA42;
font-size: 12px;
font-weight: 600;
color: #FFF;
padding: 5px;
border-color: #FFF;
border-width: 2px;
border-style: solid;
}
th {
font-family: verdana,arial,sans-serif;
background: #9A8B7D;
font-size: 13px;
font-weight: 500;
color: #FFF;
padding: 5px;
border-color: #FFF;
border-width: 2px;
border-style: solid;
}
td {
font-family: verdana,arial,sans-serif;
background: #DDD;
font-size:12px;
padding: 5px;
border-color: #FFF;
border-width: 2px;
border-style: solid;
}
</style>'
SET @body1 = '<html><head><title>DB Mail Alert</title>' + @css + '</head>
<body>
<div id ="body">
<table class = "results">
<tr>
<th class="resultsTitle" colspan="5">LONG RUNNING QUERIES</th>
</tr>
<tr>
<th>Job_name</th>
<th>Run_Date</th>
<th>Run Time</th>
</tr>'
SELECT @body1 = @body1 + '<tr>
<td>' + job_name + '</td>
<td>' + run_date + '</td>
<td>' + run_time + '</td>
</tr>'
FROM @FailedJobs
SET @body1 = @body1 +
'</table>
</div>
</body>
</html>'
SET @subject1 = 'Failed Jobs on ' + @@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'No-Reply',
@recipients = 'My email address',
@body = @body1,
@subject = @subject1,
@body_format = 'HTML'
END
June 19, 2017 at 4:55 pm
Small hint: Between the DECLARE of @cnt and your check to see if it's >0, what all do you do to that variable? 🙂
Cheers!
June 19, 2017 at 5:23 pm
Jacob Wilkins - Monday, June 19, 2017 4:55 PMSmall hint: Between the DECLARE of @cnt and your check to see if it's >0, what all do you do to that variable? 🙂Cheers!
Heh - that's a pretty good hint. 😉
June 21, 2017 at 3:25 pm
First, some housekeeping 🙂
I see you updated your question and script instead of posting a reply. I'd recommend not doing that for a couple reasons.
1) it means no one else can really follow or learn from the first set of interactions; at best the first responses will just be confusing.
2) no one gets notified and the topic doesn't get bumped if you just edit an existing post.
I only noticed that the original post had changed because I clicked on this topic by accident.
Having said all that, with the appropriate replacements (my email address, my mail profile) that code works just fine on my lab instance.
If you just mean that after running it, you see a message that says "Mail (ID: Some number) queued.", that's not an error; you'll see that if your code runs without any errors.
Did you end up getting the mail?
As a final minor point, you still have the title of the html table showing as 'LONG RUNNING QUERIES', so I'm guessing you borrowed this from an example that showed long running queries. You'll probably want to change that to 'FAILED JOBS IN LAST 24 HOURS' or whatever is appropriate.
Cheers!
June 28, 2017 at 9:39 am
Your script worked on my server . I got actually list of failed jobs in emails. Can you check your email profile name and email addresses?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply