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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply