SQL HTML email has no content but data is present

2019-08-02 02:28发布

站内问答 / 前沿技术
907 1 6

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.

1条回答
欢心
2楼-- · 2019-08-02 02:37

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))
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)