Send multiple HTML tables with sp_send_dbmail

Question

Send multiple HTML tables with sp_send_dbmail

bridgt01

SSCommitted

Points: 1890
More actions
April 30, 2010 at 6:35 am

#220390

How would someone send multiple html tables with sp_send_dbmail? In the @body paramater I can only send one table such as something like @tableHTML. I would like to be able to send more than one table in the email such as @tableHTML2 or something of the sort to display the extra table below the first one. Has anyone had experience in doing this and can advise?

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

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Create a varchar(max) variable.

Then insert into that all of your html, including however many tables you need. You can add multiple items, then just use that variable in the sp_db_mail.

Shamless self promotion - read my blog http://sirsql.net

bridgt01 SSCommitted Points: 1890 More actions · Answer 2

Here is a sample of what I'm trying to do. The email that is sent is blank:

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =

N'<H1>DB Growth</H1>' +

N'<table border="1">' +

N'<tr><th>DBName</th><th>ServerName</th>' +

N'<th>Todays_Size</th><th>Yesterdays_Size</th></tr>' +

CAST ( ( SELECT td = T.DBName, '',

td = T.ServerName, '',

td = CONVERT(int, T.Size), '',

td = CONVERT(int, Y.Size)

FROM (SELECT * FROM test..prodstats WHERE DateOnly = CONVERT(VARCHAR(10),GETDATE(),101)) As T

JOIN (SELECT * FROM test..prodstats WHERE DateOnly = CONVERT(VARCHAR(10),GETDATE() - 1,101)) As Y

ON T.DBName = Y.DBName

AND T.ServerName = Y.ServerName

WHERE T.Size - Y.Size > 1000

FOR XML PATH('tr'), ELEMENTS

) AS NVARCHAR(MAX) ) +

N'</table>' ;

DECLARE @newdb NVARCHAR(MAX) ;

SET @newdb =

N'<H1>New DB''s</H1>' +

N'<table border="1">' +

N'<tr><th>DBName</th><th>ServerName</th></tr>' +

CAST ( ( SELECT td = DBName, '',

td = Servername

FROM (SELECT Distinct 'U' AS setname, DBName, Servername FROM prodstats WHERE DateOnly = CONVERT(VARCHAR(10),GETDATE(),101)

UNION ALL

select distinct null, DBName, Servername FROM prodstats WHERE DateOnly = CONVERT(VARCHAR(10),GETDATE() -1,101) ) AS D1

GROUP BY DBName, Servername

HAVING COUNT(*) = 1

AND MAX(setname) = 'U'

FOR XML PATH('tr'), ELEMENTS

) AS NVARCHAR(MAX) ) +

N'</table>' ;

DECLARE @backupHTML NVARCHAR(MAX) ;

SET @backupHTML =

N'<H1>Missed Backups</H1>' +

N'<table border="1">' +

N'<tr><th>DBName</th><th>ServerName</th><th>Last_Full_Backup</th></tr>' +

CAST ( ( SELECT td = DBName, '',

td = Servername, '',

td = Last_Full_Backup

FROM Test..ProdStats

WHERE CONVERT(VARCHAR(10),Last_Full_Backup,101) <> CONVERT (date, CURRENT_TIMESTAMP)

AND CONVERT(VARCHAR(10),Last_Full_Backup,101) <> CONVERT (date, CURRENT_TIMESTAMP - 1)

AND DateOnly = CONVERT(VARCHAR(10),GETDATE(),101)

ORDER By DBName, Servername

FOR XML PATH('tr'), ELEMENTS

) AS NVARCHAR(MAX) ) +

N'</table>' ;

DECLARE @Mergedtable NVARCHAR(MAX) ;

SET @Mergedtable = @tableHTML & @newdb & @backupHTML

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Tim''s Email',

@recipients='tbridges@anywhere.com',

@subject = 'Growing DB''s',

@body = @Mergedtable,

@body_format = 'HTML' ;

Stefan_G SSCertifiable Points: 6609 More actions · Answer 3

It looks like you are on the right track.

My guess is that one of the variables @tableHTML @newdb @backupHTML is NULL. Add some debugging print statements to the procedure and it should not be too difficult to find out what is wrong.

bridgt01 SSCommitted Points: 1890 More actions · Answer 4

That was exactly it. Thanks for pointing me in the right direction.

s161277 SSC Rookie Points: 33 More actions · Answer 5

This issues becoz of NULL table.

I did below modification , now it is working good.

DECLARE @CONStableHTML NVARCHAR(MAX) ;

If @tableHTML is Not Null

set @CONStableHTML = @tableHTML1

If @Newdbis is Not Null

set @CONStableHTML = @CONStableHTML + @Newdbis

If @backupHTML is Not Null

set @CONStableHTML = @CONStableHTML + @backupHTML

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL.DBA',

@recipients = 'sathish@sathish.com',

@subject = 'Job Status Dashboard',

@body = @CONStableHTML ,

@body_format = 'HTML' ;