MSDB.dbo.sp_send_dbmail sending blank html body

Question

Post reply

MSDB.dbo.sp_send_dbmail sending blank html body

SQLHeap

Hall of Fame

Points: 3572
More actions
July 6, 2012 at 8:13 am

#263716

I have a procedure that is executed at last step of a job to report the previous steps' activity. When I manually executed the sp it sends the email and the body has the report in html format. But, when it's executed through the job the body is blank. Any ideas why?
DECLARE @tableHTML nvarchar(MAX), @RUN_DATE varchar(50), @BATCH_NUM int
SET @BATCH_NUM = (
SELECT *
FROM OPENQUERY(PROD, '
SELECT MAX(BATCH_NUM)
FROM SCHEMA.TABLE'
)
)
SET @RUN_DATE = LEFT((
SELECT *
FROM OPENQUERY(PROD, '
SELECT MAX(LOAD_DT)
FROM SCHEMA.TABLE'
)
), 10) + ' 20:00:00.000'
SET @tableHTML =
N'<H1>Header.</H1>' +
N'<table border="3" bordercolor="Blue">' +
N'<tr><th>BATCH_NUM</th><th>LOAD_DT</th>' +
N'<th>TABLE_NAME</th><th>ROW_COUNT</th></tr>' +
CAST((SELECT td = BATCH_NUM, ''
, td = @RUN_DATE, ''
, td = TABLE_NAME, ''
, td = ROW_COUNT, ''
FROM LINKEDSERVER.DB.dbo.TABLE
WHERE BATCH_NUM = @BATCH_NUM
For XML PATH('tr'), TYPE) as nvarchar(MAX)) +
N'</table>'
EXEC MSDB.dbo.sp_send_dbmail
@RECIPIENTS = 'email@work.com',
@SUBJECT = 'Successfull Balancing Issued Transaction Report Summary',
@BODY = @tableHTML,
@BODY_FORMAT = 'HTML',
@PROFILE_NAME = 'EmailProfile'
There is an exception to every rule, except this one...

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

print your @tableHTML variable before you get to the sp_sendmail.

is it null, maybe due to concatination? that would make your html body blank for sure.

check your one parameter and three columns for nulls?

BATCH_NUM

@RUN_DATE

TABLE_NAME

ROW_COUNT

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 2

You may have to make the query dynamic SQL (stored in a variable) and execute the variable in order to get the Body = @tableHTML part of it working correctly.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

SQLHeap Hall of Fame Points: 3572 More actions · Answer 3

Thanks for your suggestions. Turns out that in although I had Database Mail XPs enabled, it also required SQL Mail XPs to be enabled.

There is an exception to every rule, except this one...

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 4

Thank you for letting us know. That will help with future questions.

Glad you got it working.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sandy67 Newbie Points: 1 More actions · Answer 5

Hi, I am facing the same issue - can you please let me know how you enabled SQL Mail XPs please? Thanks