Sql-server – Script not emailing from Maintenance Plan/SSIS

maintenance-planssql serverssis

I have created the following script to send emails from my plan but it isn't sending out. It works correctly in a query window and under an agent job. This is added to the plan as a 'Execute T-SQL Statement Task'

MSSQL 2008 R2

USE msdb

DECLARE @EmailBody VARCHAR(MAX)
DECLARE @EmailProfile VARCHAR(100) = 'SQL_Alert'
DECLARE @Email VARCHAR(100) = 'my email is here'
DECLARE @JobName VARCHAR(200) = 'job name is here'
DECLARE @Subject VARCHAR(200) = @JobName + ' Status: '

;WITH CTE
AS 
(
SELECT distinct top 1 sj.name AS [Agent Job Name],
CASE WHEN sjh.run_status=0 THEN 'Failed'
                 WHEN sjh.run_status=1 THEN 'Succeeded'
                 WHEN sjh.run_status=2 THEN 'Retry'
                 WHEN sjh.run_status=3 THEN 'Cancelled'
           ELSE 'Unknown'  
      END [Job Outcome],
sja.run_requested_date, 
CONVERT(VARCHAR(12), sja.stop_execution_date-sja.start_execution_date, 114) Duration
FROM    msdb.dbo.sysjobactivity sja
INNER   JOIN msdb.dbo.sysjobs sj
ON  sja.job_id = sj.job_id
INNER   JOIN msdb.dbo.sysjobhistory sjh
ON  sjh.job_id = sj.job_id
WHERE   sja.run_requested_date IS NOT NULL
AND sj.name = @JobName
AND sjh.step_id = 0
)

SELECT @EmailBody = 
'Agent Name: '
 + CHAR(9)
 + (SELECT [Agent Job Name] FROM CTE) 
 + CHAR(13)
 + 'Job Outcome: ' 
 + CHAR(9)
 + (SELECT [Job Outcome] FROM CTE)
 + CHAR (13)
 + 'Ran At: ' 
 + CHAR(9)
 + CONVERT(VARCHAR(100), (SELECT run_requested_date FROM CTE), 25)
 + CHAR (13)
 + 'Duration: ' 
 + CHAR(9)
 + (SELECT [Duration] FROM CTE)

IF (@EmailBody IS NOT NULL)
BEGIN
SET @Subject = @Subject +
CASE WHEN @EmailBody LIKE '%Failed%' THEN 'Failed'
     WHEN @EmailBody LIKE '%Succeeded%'  THEN 'Succeeded'
     WHEN @EmailBody LIKE '%Retry%' THEN 'Retry'
     WHEN @EmailBody LIKE '%Cancelled%' THEN 'Cancelled'
     ELSE 'Unknown'
     END;

EXEC sp_send_dbmail 
@profile_name = @EmailProfile,
@recipients = @Email,
@subject = @Subject,
@Body = @EmailBody
END

Results from history

Executing query "USE msdb DECLARE @EmailBody VARCHAR(MAX) DECLA…".: 100% complete End Progress DTExec: The package execution returned DTSER_SUCCESS (0). Started: 11:38:55 AM Finished: 11:38:58 AM Elapsed: 2.995 seconds. The package executed successfully. The step succeeded.

Best Answer

Currently, your code will not send any email if any fields used to create the subject or body are NULL.

I'd add a case to deal with that such as:

IF (@EmailBody IS NOT NULL)
BEGIN
    SET @Subject = @Subject +
    CASE WHEN @EmailBody LIKE '%Failed%' THEN 'Failed'
         WHEN @EmailBody LIKE '%Succeeded%'  THEN 'Succeeded'
         WHEN @EmailBody LIKE '%Retry%' THEN 'Retry'
         WHEN @EmailBody LIKE '%Cancelled%' THEN 'Cancelled'
         ELSE 'Unknown'
         END;

    EXEC sp_send_dbmail 
    @profile_name = @EmailProfile,
    @recipients = @Email,
    @subject = @Subject,
    @Body = @EmailBody
END
ELSE /* catch any issues with NULL for @EmailBody */
BEGIN
    SET @EmailBody = 'nothing to report';
    SET @Subject = 'alert!';
    EXEC sp_send_dbmail 
    @profile_name = @EmailProfile,
    @recipients = @Email,
    @subject = @Subject,
    @Body = @EmailBody
END
Related Question