Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Email in HTML format with sp_send_dbmail

Status
Not open for further replies.
Joined
Apr 18, 2002
Messages
185
Location
US
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
 
So what is the solution to get around this??
 
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
 
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.
 
?????

Code:
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
 
Perfect! That works...

Thanks for all your help!!!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top