SQL Agent Job - mail body is blank

Question

Post reply

SQL Agent Job - mail body is blank

jj_za

SSC Veteran

Points: 232
More actions
December 18, 2015 at 12:38 am

#306342

Greetings. My first post.
Background
Our DB has a heap of jobs that need to run every day.
We want to email the job results, in table format, using HTML to the Ops Support people.
We've created a stored proc that collects the data from MSDB and inserts the results into an HTML email body, in table format.
The stored proc includes this
exec msdb.dbo.sp_send_dbmail
to email the results.
Problem
If we execute the stored proc directly - works perfectly 😎
If we create a SQL Agent Job and schedule to execute the stored proc - email arrives but body is blank 🙁
What we've tried
We put the query directly into the step of the job - same problem
We can see in msdb.dbo.sysmail_mailitems that the item was queued however the "body" field is NULL. It's only NULL when the mail is deliver via the job. When the stored proc runs directly via query, the "body" fields contains data.
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[ReportList] Script Date: 2015/12/18 07:40:25 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ReportList]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @MAIL_BODY VARCHAR(max)
/* HEADER */
SET @MAIL_BODY='
Good day All,
Here is the list of all the jobs on PerFIX DB and their status for today.'
/* tr means Table Row */
/* th means Table Header */
/* td means Table Data*/
/* h3 means HTML Header format text heading. Title of the table*/
SELECT @MAIL_BODY = @MAIL_BODY + '<h3>SUCCESSFUL JOBS</h3><table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:consolas;text-align:center;">' +
'<tr>
<th>Job Name</th>
<th>Run Status</th>
<th>Last Executed Date</th>
</tr>'
/* ROWS */
/* insert directly into the table rows the results from the select*/
/* td only takes text so convert to varchar*/
/* Only return rows where Case when job is a 'Success' */
/* Only return rows for jobs that have run today and if job is Perfix-related then also use 'jb.category_id < 103' */
/* jb.category_id < 103 means anything higher that 103 is usually a database maintenance job*/
SELECT
@MAIL_BODY = @MAIL_BODY +
'<tr>' +
'<td>' + jb.[name] + '</td>' +
'<td>' + CASE hi.[run_status] WHEN 1 THEN 'Success'WHEN 0 THEN 'Fail' END + '</td>' + --Show result as Success of Fail
'<td>' + Max(CAST(CAST(jbs.[last_executed_step_date] as datetime) AS VARCHAR(30))) + '</td>' +
'</tr>'
FROM
[msdb].[dbo].[sysjobs] as jb
inner join [msdb].[dbo].[sysjobactivity] as jbs on jbs.job_id=jb.job_id --joining up all the relevant data
inner join [msdb].[dbo].[sysjobhistory] as hi on hi.job_id=jbs.job_id
where
convert(date,jbs.run_requested_date) = convert(date,GETDATE())
and run_status= 1 --
and jb.category_id < 103 --looking for jobs that are created by humans and have not been categorized
group by jb.[name], hi.[run_status], jbs.[last_executed_step_date]
order by jbs.[last_executed_step_date] desc --sort it from oldest to newest
SELECT
@MAIL_BODY = @MAIL_BODY + '</table>
'
SELECT
@MAIL_BODY = @MAIL_BODY + '<h3>FAILED JOBS</h3><table border="1" align="center" cellpadding="2" cellspacing="0" style="color:red;font-family:consolas;text-align:center;">' +
'<tr>
<th>Job Name</th>
<th>Run Status</th>
<th>Last Executed Date</th>
</tr>'
/* ROWS */
SELECT
@MAIL_BODY = @MAIL_BODY +
'<tr>' +
'<td>' + jb.[name] + '</td>' +
'<td>' + CASE hi.[run_status] WHEN 1 THEN 'Success'WHEN 0 THEN 'Fail' END + '</td>' +
'<td>' + Max(CAST(CAST(jbs.[last_executed_step_date] as datetime) AS VARCHAR(30))) + '</td>' +
'</tr>'
FROM
[msdb].[dbo].[sysjobs] as jb
inner join [msdb].[dbo].[sysjobactivity] as jbs on jbs.job_id=jb.job_id
inner join [msdb].[dbo].[sysjobhistory] as hi on hi.job_id=jbs.job_id
where
convert(date,jbs.run_requested_date) = convert(date,GETDATE())
and run_status= 0
and (jbs.[last_executed_step_date] is not null)
and jb.category_id < 103
group by jb.[name], hi.[run_status], jbs.[last_executed_step_date]
order by jbs.[last_executed_step_date] desc
SELECT
@MAIL_BODY = @MAIL_BODY + '</table>'
SELECT
@MAIL_BODY = @MAIL_BODY + '<h3>JOBS THAT DID NOT RUN TODAY</h3> <table border="1" align="center" cellpadding="2" cellspacing="0" style="color:black;font-family:consolas;text-align:center;">' +
'<tr>
<th>Job Name</th>
<th>Last Executed Date</th>
</tr>'
SELECT
@MAIL_BODY = @MAIL_BODY +
'<tr>' +
'<td>' + jb.[name] + '</td>' +
'<td>' + CAST(CAST(Max(jbs.[last_executed_step_date]) as datetime) AS VARCHAR(30)) + '</td>' +
'</tr>'
FROM
[msdb].[dbo].[sysjobs] as jb
inner join [msdb].[dbo].[sysjobactivity] as jbs on jbs.job_id = jb.job_id
WHERE
(jbs.[last_executed_step_date] is not null) and jb.category_id < 103
and jbs.job_id not in
(
select ja.job_id
from [msdb].[dbo].[sysjobs] as j inner join [msdb].[dbo].[sysjobactivity] as ja on ja.job_id = j.job_id
where (jbs.[last_executed_step_date] is not null) and convert(date,ja.run_requested_date) = convert(date,GETDATE()))
group by jb.[name] order by Max(jbs.[last_executed_step_date]
) desc
SELECT @MAIL_BODY = @MAIL_BODY + '</table> </br>
Kind Regards,' +
'
ZA1-CT-PFIXDB'
EXEC
msdb.dbo.sp_send_dbmail
@profile_name = 'supportInternal',
@recipients = 'me@someplace.com',
@subject = 'subject',
@body = @MAIL_BODY,
@importance='high',
@body_format='HTML'
END
If any one has any suggestions as to what causes the email body to return NULL only on SQL Server Job, please let me know.
Many Thanks!
[font="Arial"]all things in IT are possible, just find a way to do it[/font]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Brad Feaker-195979 SSChasing Mays Points: 639 More actions · Answer 1

I have had issues in the past when I used VARCHAR(MAX) as the data type for the body of an HTML email.

Try using NVARCHAR(MAX) instead...here is a little sample code of how I do it and it works like a charm...

DECLARE@tableHTML NVARCHAR(MAX);

SET @tableHTML = N'<H1>Test</H1>' + N'<table border="1">'

+ N'<tr><th>Table Name</th><th>Count</th>'

+ N'<th>Tot Inv</th><th>Tot Paid</th><th>As Of Date</th></tr>'

+ CAST((SELECTtd = ISNULL(.[TableName], ''),

'',

td = ISNULL(.[Count], ''),

'',

td = ISNULL(.[TotalInvoices], ''),

'',

td = ISNULL(.[TotalPaid], ''),

'',

td = ISNULL(.[AsOfDate], ''),

''

FROM[dbo].[SanityCheck] s

ORDER BY .[AsOfDate],

.[TableName]

FOR

XMLPATH('tr'),

TYPE

) AS NVARCHAR(MAX)) + N'</table>';

EXEC [msdb].[dbo].[sp_send_dbmail]

@recipients = 'someone@somewhere.com',

@subject = 'TEST',

@body = @tableHTML,

@body_format = 'HTML';

Brad Feaker"Tantum religio potuit suadere malorum." - Lucretius

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

Consider this and the symptoms...

If the email job is currently running, will any of the data that you've selected come back as NULL? ANY concatenation of NULL will wipe out everything in the @MAIL_BODY variable. That might be why it works when you run it from a script or a proc... it doesn't report on itself that way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)