SQL HTML email has no content but data is present in table

Question

I am sending a HTML email that contains some rows from a table. I am using a temporary table @tableupdate which definitely has data in it when I run the script.

Everything runs fine and I get the email, it just has no content whatsoever. When I view the source of the email it's blank. Can anyone see where I have gone wrong?

To help diagnose the problem, I've been outputting the contents of the variable containing all the HTML in it at various points and it seems to be empty after this part:

select @tableHTML = @tableHTML + @body 
        + '</table></div></body></html>'

@body doesn't seem to populate at all. I'm just not sure what to change to make it work.

The relevant piece of code, including comments to show where the data disappears:

select * from @tableupdate --rows are returned

declare @emailSubject varchar(100),
    @textTitle varchar(100),
    @tableHTML nvarchar(max)

select @textTitle = 'Test table'

set  @tableHTML = '<html><head><style>' +
   'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
   '</style></head><body>' 
   + '<div style="margin-top:20px; margin-left:5px; margin-bottom:15px; font-weight:bold; font-size:1.3em; font-family:tahoma;">' +
   @textTitle + '</div>' 
   + '<div style="margin-left:50px; font-family:tahoma;"><table cellpadding=0 cellspacing=0 border=0>' +
        '<tr bgcolor=#4b6c9e>' 
            + '<td align=center><font face="calibri" color=White><b>Col1</b></font></td>'     
            + '<td align=center><font face="calibri" color=White><b>Col2</b></font></td>'    
            + '<td align=center><font face="calibri" color=White><b>Col3</b></font></td>'  
            + '<td align=center><font face="calibri" color=White><b>Col4</b></font></td>'   
            + '<td align=center><font face="calibri" color=White><b>Col5</b></font></td>'
            + 
        '</tr>' 
select @tablehtml --has the correct value at this point

select @body =
(
   select ROW_NUMBER() over(order by id) % 2 as TRRow,
           td = col1,     
           td = col2,      
           td = col3,
           td = col4,
           td = col5        
   from @tableUpdate 
   where notificationType = 'NEWDATE'         
   order by clname
   for XML raw('tr'), elements
)
select @body --empty?!
set @body = REPLACE(@body, '<td>', '<td align=center><font face="tahoma">')
set @body = REPLACE(@body, '</td>', '</font></td>')
set @body = REPLACE(@body, '_x0020_', space(1))
set @body = Replace(@body, '_x003D_', '=')
set @body = Replace(@body, '<tr><TRRow>0</TRRow>', '<tr bgcolor=#F8F8FD>')
set @body = Replace(@body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#EEEEF4>')
set @body = Replace(@body, '<TRRow>0</TRRow>', '')
select @body --still empty

select @tableHTML = @tableHTML + @body 
        + '</table></div></body></html>'

select @tablehtml --now empty!

select @tableHTML = '<div style="color:Black; font-size:11pt; font-family:tahoma; width:100px;">' + @tableHTML + '</div>'

select @tableHTML --still empty

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @databaseMailProfileName,
    @body = @tableHTML,
    @body_format ='HTML',
    @recipients = 'me@me.com',
    @subject = 'Subject' ;

If I simply pass select * from @tableupdate or similar to the email, it sends me the data in a not very nice format.

As you were told already, this is a `NULL` related issue. In general it is not a good idea, build XML or (X)HTML on string level. This can have various unexpected side effects. I'd like to point [to this answer](https://stackoverflow.com/a/39487565/5089204) which provides a very powerful way to create a HTML table out of any `SELECT` (blank- and null-safe). [At this answer](https://stackoverflow.com/a/50075801/5089204) you find a fully working example. — Shnugo, May 08 '18 at 14:15

S3S · Accepted Answer · 2018-05-08T13:30:53.610

The issue is the query below, returns NULL.

select @body =
(
   select ROW_NUMBER() over(order by id) % 2 as TRRow,
           td = col1,     
           td = col2,      
           td = col3,
           td = col4,
           td = col5        
   from @tableUpdate 
   where notificationType = 'NEWDATE'         
   order by clname
   for XML raw('tr'), elements
)

Focus on this single query, and you will solve your issue. Without sample data of @tableUpdate, we can't solve your problem. However, since @body is NULL, concatenating anything to it will also yield NULL.

For example, select 1 + null, 'this string' + null will return two NULLs.

Thus, all of these set operators will still yield NULL for @body

set @body = REPLACE(@body, '<td>', '<td align=center><font face="tahoma">')
set @body = REPLACE(@body, '</td>', '</font></td>')
set @body = REPLACE(@body, '_x0020_', space(1))
set @body = Replace(@body, '_x003D_', '=')
set @body = Replace(@body, '<tr><TRRow>0</TRRow>', '<tr bgcolor=#F8F8FD>')
set @body = Replace(@body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#EEEEF4>')
set @body = Replace(@body, '<TRRow>0</TRRow>', '')
select @body --still empty

Then, you set @tableHTML to NULL by concatenating it with @body because @body IS NULL

select @tableHTML = @tableHTML + @body 
        + '</table></div></body></html>'

Which leaves @tableHTML and @body with the value of NULL.

A stab at fixing this, since you are building a table, would be to set @body like so:

select @body =
   cast((select ROW_NUMBER() over(order by id) % 2 as 'td',
           '',
           isnull(col1,'') as 'td',
           '',     
           isnull(col2,'') as 'td',
           '',      
           isnull(col3,'') as 'td',
           '',
           isnull(col4,'') as 'td',
           '',
           isnull(col5,'') as 'td'        
   from @tableUpdate 
   where notificationType = 'NEWDATE'         
   order by clname
   for XML path('tr'), elements) as nvarchar(max))

Thanks! This has fixed it. Although I am missing a column heading from the body part. I think it's `cast((select ROW_NUMBER() over(order by id) % 2 as 'td',` that needs to have a word inserted somewhere. I'll try to figure that out. — Syntax Error, May 08 '18 at 13:44
So, usually i set the headers **after**. I can show you that if you need--i'd just open a new question on it. Glad this worked tho! — S3S, May 08 '18 at 13:45

SQL HTML email has no content but data is present in table

1 Answers1

Linked