Add Variable in body of DBMail - SQL Server

Question

When adding a my variable @dateDifference to the message of the body, the body comes up blank on my Outlook. However, if I remove the variable, the message is printed in the email.

Below is my code:

DECLARE @sql                NVARCHAR(255)
DECLARE @dateDifference     NVARCHAR(255)
DECLARE @bodyMessage        NVARCHAR(MAX)

SET @sql = (SELECT backupFileDate 
                FROM #checkBackupTime 
                WHERE backupFileDate >= DATEADD(HH, -1, GETDATE()))

SET @dateDifference = (SELECT DATEDIFF(HOUR, @sql, CURRENT_TIMESTAMP))

SET @bodyMessage = 'The lastest backup file is ' + @dateDifference + ' hours old'

IF @sql IS NULL OR @sql = '' 
    BEGIN

        EXEC msdb.dbo.sp_send_dbmail 
            @recipients = '[email protected]',
            @subject = N'mySubject',
            @body = @bodyMessage;
    END

Any ideas?

Your choice of column names confuses me... why is the BackupFileName a DATETIME? Why is the BackupFileName being set to a variable named @sql? The nomenclature used here is perplexing... — Siyual
– Siyual, Commented Jan 11, 2017 at 21:36

Siyual · Accepted Answer · 2017-01-11 21:58:34Z

The naming convention used here is... confusing. Nevertheless, there are several things wrong with your query:

The following code will break if there was more than one backupFileName within the previous hour.

SET @sql = (SELECT backupFileName
FROM #checkBackupTime 
WHERE backupFileName >= DATEADD(HH, -1, GETDATE()))

In the above code, you are only ever checking for backupFileNames in the past hour (more on that in #3).
The following code can only ever return one of three values: NULL, 0, or 1. The reason it can only ever be those three values is because you are limiting the results to just those files in the previous hour, and then asking how many hours old it is. It will realistically only ever be 0 or NULL (in the case that there were no backups made in the last hour).

SET @dateDifference = (SELECT DATEDIFF(HOUR, @sql, CURRENT_TIMESTAMP))

The following code should generate an error. You need to convert the @dateDifference to a VARCHAR:

SET @bodyMessage = 'The lastest backup file is ' + @dateDifference + ' hours old'

Because the value can only ever be NULL or 0, @bodyMessage would end up being NULL if @dateDifference is NULL. (a VARCHAR + NULL = NULL).
You are only ever sending the email if @SQL IS NULL or ''! You complain about your email coming out blank, but you're only ever sending it if it is!

IF @sql IS NULL OR @sql = '' 
BEGIN
    EXEC msdb.dbo.sp_send_dbmail 
    ...

Proposed changes

Remove the time constraint and grab the MAX(backupFileName):

Select @Sql = Max(backupFileName)
From   #checkBackupTime

Use COALESCE() and CONVERT() for the value:

SET @bodyMessage = 'The lastest backup file is ' 
                       + Coalesce(Convert(Varchar, @dateDifference), '') 
                       + ' hours old'

And change the email condition to check the time since the last backup:

If @DateDifference > 1 
Begin
    Exec msdb.dbo.sp_send_dbmail 
    ...

Thanks, made the suggested changes and everything seems to work.

Sefe · Accepted Answer · 2017-01-11 21:54:26Z

1

If @datedifference is NULL the whole expression is NULL. Try:

SET @bodyMessage = 'The lastest backup file is ' + ISNULL(@dateDifference, '') + ' hours old'

answered Jan 11, 2017 at 21:54

Sefe

14.1k6 gold badges45 silver badges56 bronze badges

Collectives™ on Stack Overflow

Add Variable in body of DBMail - SQL Server

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related