April 23, 2015 at 4:01 am
Hello Experts,
I wanted to send multiple HTML tables using sp_send_dbmail with below query. But I get blank result.If I use one of the HTML set I am getting my output but not using both. Am I missing something in my query?
Please help..
DECLARE @tableHTML1 NVARCHAR(MAX);
DECLARE @tableHTML2 NVARCHAR(MAX);
DECLARE @tableHTML NVARCHAR(MAX) = @tableHTML1 + @tableHTML2;
SET @tableHTML1 =
N'<H1>** Subject1 ** </H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>User Name</th>' +
N'<th>Enabled</th></tr>' +
CAST ( ( SELECT td =[CurentDatabase], '',
td = [Name], '',
td = [Enabled]
FROM xyz
where CurentDatabase not in ('master','tempdb','model','msdb') and Enabled='Yes'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
SET @tableHTML2 =
N'<H1>** Subject2** </H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>User Name</th>' +
N'<th>Enabled</th></tr>' +
CAST ( ( SELECT td =[CurentDatabase], '',
td = [Name], '',
td = [Enabled]
FROM xyz1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
exec msdb.dbo.sp_send_dbmail @profile_name = 'DBmail',
@recipients='abc@xx.com',
@subject = '** Subject ** ',
@body = @tableHTML,
@body_format = 'HTML',
@query_result_header = 0;
April 23, 2015 at 5:20 am
Issue 1: you are setting the value of @tableHTML before you set @tableHTML1 and @tableHTML2. Hopeflly that happened when you tried to prepare the code for posting on this forum.
Issue 2: theory > something you are trying to concatenate into @tableHTML2 is NULL making the entire string NULL. Note: because I cannot see your schema I made some assumptions about NULLability and data types for your columns. You do not have the protection from NULL in building HTML Table 1 but it might not be a bad idea to add it there as well to be consistent and protect against changing data conditions.
See some inline changes I made:
DECLARE @tableHTML1 NVARCHAR(MAX);
DECLARE @tableHTML2 NVARCHAR(MAX);
DECLARE @tableHTML NVARCHAR(MAX); -- changed for Issue 1
SET @tableHTML1 =
N'<H1>** Subject1 ** </H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>User Name</th>' +
N'<th>Enabled</th></tr>' +
CAST ( ( SELECT td =[CurentDatabase], '',
td = [Name],
'',
td = [Enabled]
FROM xyz
where CurentDatabase not in ('master','tempdb','model','msdb') and Enabled='Yes'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
SET @tableHTML2 =
N'<H1>** Subject2** </H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>User Name</th>' +
N'<th>Enabled</th></tr>' +
CAST (
(
SELECT td =[CurentDatabase], '',
td = ISNULL([Name], ''), -- changed for Issue 2
'',
td = ISNULL([Enabled], 0) -- changed for Issue 2 (assuming column is NULLable and is a number type)
FROM xyz1
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) + N'</table>' ;
SET @tableHTML = @tableHTML1 + @tableHTML2; -- changed for Issue 1
exec msdb.dbo.sp_send_dbmail @profile_name = 'DBmail',
@recipients='abc@xx.com',
@subject = '** Subject ** ',
@body = @tableHTML,
@body_format = 'HTML',
@query_result_header = 0;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 29, 2015 at 6:01 am
Thanks Orlando Colamatteo.....first option worked for me...
April 30, 2015 at 9:38 am
My only recommendation, assuming you are using SQL 2012, would be to use a CONCAT(@tableHTML1, @tableHTML2) instead of:
= @tableHTML1 + @tableHTML2;
Because if one of the tables is null, CONCAT_NULL_YIELDS_NULL could make your @tableHTML null and you would send blank results.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy