Trying to create EMAIL formatted HTML with T-SQL

Question

Post reply

Trying to create EMAIL formatted HTML with T-SQL

Isabelle2378

SSCertifiable

Points: 6174
More actions
April 7, 2016 at 12:26 pm

#313155

Hi,
I am trying to learn how to use T-SQL to format an HTML table and email it to me. I've seen lots of examples and got most of it setup, but the results are sending me an email that is empty. Here is the T-SQL that I'm using...an example to list the latest Database Backups:
use msdb
GO
;WITH LatestBackupSet AS (
SELECT
b.machine_name,
b.database_name as DBName,
b.backup_start_date,
b.backup_finish_date,
CASE
WHEN b.[type] = 'D' THEN 'Database'
WHEN b.[type] = 'I' THEN 'Differential database'
WHEN b.[type] = 'L' THEN 'Log'
ELSE b.[type]
END Backup_Type,
DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
b.recovery_model,
b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
bf.physical_device_name as Location,
ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
FROM
msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
ON b.media_set_id=bf.media_set_id
)
SELECT
machine_name,
DBName,
backup_start_date,
backup_finish_date,
Backup_Type,
Total_Time_in_Minute,
recovery_model,
Total_Size_GB,
Location
FROM
LatestBackupSet AS lbs
WHERE
lbs.Rnk = 1
ORDER BY
lbs.DBName DESC
GO
So then I changed the WITH clause to just use a temp table and ended up with this...it works and sends me an email but there is no data. When it executes, I get a message (8 row(s) affected)
Mail (Id: 9) queued. so it seems to be working but the email just has the table headers. Any ideas?
DECLARE @html nvarchar(max), @table nvarchar(max), @subject nvarchar(max);
SET @subject = 'Database Backups';
SET @html =
N'<html><head><title>Duplicate Orders</title></head>' + CHAR(10) +
N'<body style="font-family: Arial">' +
N'<h2>Database Backups</h2>' +
'<table border="2" cellspacing="2" cellpadding="2">' +
N'<tr bgcolor=#FFEFD8>' +
N'<th width="120">Machine Name</th>' +
N'<th width="180">DBName</th>' +
N'<th width="150">Backup Start Date</th>' +
N'<th width="150" align="right">Backup Finish Date</th>' +
N'<th width="90" align="right">Backup Type</th>' +
N'<th width="90" align="right">Total Time</th>' +
N'<th width="90" align="right">Recovery Model</th>' +
N'<th width="90" align="right">Size in GB</th>' +
N'<th width="180" align="right">Location</th>' +
N'</tr>' + CHAR(10);
SELECT
b.machine_name,
b.database_name as DBName,
b.backup_start_date,
b.backup_finish_date,
CASE
WHEN b.[type] = 'D' THEN 'Database'
WHEN b.[type] = 'I' THEN 'Differential database'
WHEN b.[type] = 'L' THEN 'Log'
ELSE b.[type]
END Backup_Type,
DATEDIFF(MINUTE,b.backup_start_date ,b.backup_finish_date) as Total_Time_in_Minute,
b.recovery_model,
b.backup_size/(1024 * 1024 * 1024) as Total_Size_GB,
bf.physical_device_name as Location,
ROW_NUMBER() OVER(PARTITION BY b.database_name ORDER BY b.backup_start_date DESC) Rnk
INTO #tmp1
FROM
msdb.dbo.backupset AS b
INNER JOIN msdb.dbo.backupmediafamily AS bf
ON b.media_set_id=bf.media_set_id
SELECT @table =
CONVERT(nvarchar(max),
(SELECT td = [machine_name],'',
td = [DBName],'',
td = [backup_start_date],'',
td = [backup_finish_date] ,'',
td = [Backup_Type] ,'',
td = [Total_Time_in_Minute] ,'',
td = [recovery_model] ,'',
td = [Total_Size_GB] ,'',
td = [Location]
FROM #tmp1 AS lbs
WHERE lbs.Rnk = 1
ORDER BY lbs.DBName DESC
FOR XML PATH('tr'), TYPE
));
EXEC msdb.dbo.sp_send_dbmail @recipients='bisabelle@jellybelly.com',
@subject = @subject,
@body = @html,
@body_format = 'HTML' ;
DROP TABLE #tmp1
Thanks!!!
Isabelle
Thanks!
Bea Isabelle

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

you assigned your string to @table, but did not append it to @html;

add SET @html = @html + @table + @tail before you do the email;

you need a @tail with closing "</table></body></html>", right?

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!

Isabelle2378 SSCertifiable Points: 6174 More actions · Answer 2

Awesome! That did it. 😀

Thank you Lowell.

- Isabelle

Thanks!
Bea Isabelle