CODEWITHSUNDEEP
sql-servert-sql
AznDevil92
AznDevil92

Reputation: 554

Add Variable in body of DBMail - SQL Server

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?

Upvotes: 0

Views: 1812

Answers (2)

Siyual
Siyual

Reputation: 16917

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

  1. 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()))
  1. In the above code, you are only ever checking for backupFileNames in the past hour (more on that in #3).
  2. 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))
  1. 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'

  1. Because the value can only ever be NULL or 0, @bodyMessage would end up being NULL if @dateDifference is NULL. (a VARCHAR + NULL = NULL).

  2. 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 
    ...

Upvotes: 2

Sefe
Sefe

Reputation: 14007

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

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

Upvotes: 1

Related Questions