SQL Server Reporting emails not showing anything in body of email

Question

I have a query that will look at will look at the databases on my SQL environment and send the results to an email address. Problem is nothing is showing up in the body of the email. I am using the following query to get disk space usage:

create table #DriveSpaceLeft (Drive varchar(10),
                                  [MB Free] bigint )

insert #DriveSpaceLeft (Drive, [MB Free])
   EXEC master.dbo.xp_fixeddrives;

create table DrivesWithIssue (Drive varchar(10),
                              [MB Free] bigint )

insert into DrivesWithIssue 
  select Drive, [MB Free] from #DriveSpaceLeft
  where [MB Free] < 1000

drop table #DriveSpaceLeft

declare @cnt int  
select @cnt=COUNT(1) from DrivesWithIssue
if (@cnt > 0)
begin

    declare @strsubject varchar(100)
    select @strsubject='Check drive space on ' + @@SERVERNAME

    declare @tableHTML  nvarchar(max);
    set @tableHTML =
        N'<H1>Drives with less that 1GB Free  - ' + @@SERVERNAME + '</H1>' +
        N'<table border="1">' +
        N'<tr><th>Drive</th>' +
        N'<th>MB Free</th></tr>' +
        CAST ( ( SELECT td = [Drive], '',
                        td = [MB Free]
                  FROM DrivesWithIssue
                  FOR XML PATH('tr'), TYPE 
        ) AS NVARCHAR(MAX) ) +
        N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail
    @from_address='test@test.com',
    @recipients='test@test.com',
    @subject = @strsubject,
    @body = @tableHTML,
    @body_format = 'HTML' ,
    @profile_name='test profile'
end

drop table DrivesWithIssue

Then this step sends out the email:

declare @strsubject varchar(100)
select @strsubject='SQL check completed on ' + @@SERVERNAME

EXEC msdb.dbo.sp_send_dbmail
@from_address='x@x.com',
@recipients='x@x.com',
@subject = @strsubject,
@body_format = 'HTML',
@profile_name='test'

Might be, that you are just missing to pass over your variable `@body`? — Shnugo, Dec 14 '16 at 14:38
Btw: [This answer](http://stackoverflow.com/a/39487565/5089204) shows a fully generic approach to create the `html`-table directly from your statement... XML or HTML via string-concatenation is always - uhm - ugly (at least quite erronous)... — Shnugo, Dec 14 '16 at 14:39

score 0 · Answer 1 · answered Dec 14 '16 at 21:39

If you could get the email, then it means there is no problem with your database email setting. And you do not see anything in the email body, then it is very likely that you have some html code issue. I am guessing you forgot to add heading part <html><body>, and tail part '', so your html should be @heading + @tableHTML +@tail, do not forget to declare these two parameters before

SQL Server Reporting emails not showing anything in body of email

1 Answers1