0

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.

  • 1
    Does the job history show any errors or information messages about this part of the job? Can you put this script in a simple Agent Job and see if it runs? – Max Vernon Aug 12 '14 at 15:47
  • Updated, no errors reported – Tsukasa Aug 12 '14 at 15:50
  • 2
    SELECT TOP 30 * FROM msdb.dbo.sysmail_mailitems AS SM ORDER BY SM.mailitem_id desc; This will pull back the last 30 emails the system tried to send. Do you see your missing emails there? What is the sent_status? – billinkc Aug 12 '14 at 17:29
  • 2
    What happens if you run the query in a query window when logged in as the SQL Server Agent service account? How about using sysinternals tools to see what account is being used to run DTExec? – Aaron Bertrand Aug 12 '14 at 17:29
  • @billinkc there was no attempt of it sending an email – Tsukasa Aug 12 '14 at 20:09
3

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
  • Correct I don't want an email if anything is null, if this is the case the job has yet to run which shouldn't ever be the case. The query does return results. I have change it just to make sure but this wasn't the case and still no email. – Tsukasa Aug 12 '14 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.