Email in HTML format with sp_send_dbmail

[Steffi1013]

I am setting up an job that I want to email only if there is a result set. It works if I do not use HTML as the @body_format... but when I use the HTML formatting, it sends the email even if there is no results... How can I make this work using HTML formatting? see code below


DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Log Backup Issues</H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>Last Log Backup Time</th>' +
CAST ( ( SELECT
td = database_name, '',
td = CAST(max(backup_finish_date) AS NVARCHAR(30)), ''
FROM msdb.dbo.backupset
WHERE type = 'L'
GROUP BY database_name
HAVING CAST(max(backup_finish_date) AS NVARCHAR(30)) < DATEADD(hh, -1, GETDATE())
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

IF @@ROWCOUNT > 0
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'steph@tuttt.com',
@subject = 'Log Backup Issues',
@importance = 'High',
@body = @tableHTML,
@body_format = 'HTML';
end

 

[SQLDenis]

run this and you will know why

declare @ int
set @ =1
if @@rowcount = 1
print 'yes'
print (@@rowcount)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Steffi1013]

So what is the solution to get around this??

 

[SQLDenis]

if exists(query here)
begin
send mail
end

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Steffi1013]

I must have been beaten with the "stupid stick" today cause I am getting syntax errors and I don't see why..PLEASE HELP


IF EXISTS(
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Log Backup Issues</H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>Last Log Backup Time</th>' +
CAST ( ( SELECT
td = database_name, '',
td = CAST(max(backup_finish_date) AS NVARCHAR(30)), ''
FROM msdb.dbo.backupset
WHERE type = 'L'
GROUP BY database_name
HAVING CAST(max(backup_finish_date) AS NVARCHAR(30)) < DATEADD(hh, -1, GETDATE())
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

)
BEGIN

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'steph@tuttt.com',
@subject = 'Log Backup Issues',
@importance = 'High',
@body = @tableHTML,
@body_format = 'HTML';
end

 

[SQLDenis]

you can't have all that formatting junk in the exists it has to be a normal query

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

hint:

IF EXISTS(SELECT max(backup_finish_date)
FROM msdb.dbo.backupset
WHERE type = 'L'
GROUP BY database_name
HAVING max(backup_finish_date) < DATEADD(hh, -1, GETDATE()))

print
'yes'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Steffi1013]

Thanks... I knew I could do it that way, but I wanted it to look "pretty" in the email and a little more readable, which is why I was trying to do the HTML formatting.

 

[SQLDenis]

?????

IF EXISTS(SELECT  max(backup_finish_date)  
FROM msdb.dbo.backupset 
    WHERE type = 'L' 
    GROUP BY database_name 
    HAVING max(backup_finish_date) <   DATEADD(hh, -1, GETDATE()))
begin
DECLARE @tableHTML  NVARCHAR(MAX) ;
SET @tableHTML =
    N'<H1>Log Backup Issues</H1>' +
    N'<table border="1">' +
    N'<tr><th>Database Name</th><th>Last Log Backup Time</th>' +
    CAST ( ( SELECT      
    td = database_name, '',
td = CAST(max(backup_finish_date) AS NVARCHAR(30)), ''
FROM msdb.dbo.backupset 
    WHERE type = 'L' 
    GROUP BY database_name 
    HAVING CAST(max(backup_finish_date) AS NVARCHAR(30)) <   DATEADD(hh, -1, GETDATE())
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;
    


EXEC msdb.dbo.sp_send_dbmail 
    @recipients = 'steph@tuttt.com',
    @subject = 'Log Backup Issues',
    @importance = 'High',
    @body = @tableHTML,
    @body_format = 'HTML';
end

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Steffi1013]

Perfect! That works...

Thanks for all your help!!!