Script to send an email based on contents of a job step's output file

Michael Lacy, 2013-05-09 (first published: 2008-09-04)

I frequently use the Advanced option on SQL Server Agent Job steps to declare an output file for the job's results. Sometimes, I then use a separate job step to email this output file to users. I have had a job, that I run nightly, that looks to see if a certain kind of error exists in some database tables. It is very rare for this error to occur, yet I was sending the job's output file, as an attachment, to my users every day. Of course this can cause recipients to become immune to the contents of the email (if you cry wolf too often.....). I decided to see if I could somehow check the contents of the outputted file to see if it contained a "(0 rows(s) affected)", statement. If it does contains a row that reads: "(0 rows(s) affected)", then we don't want to send an email. You could, of course, check for a different string value.

SET nocount ON DECLARE @file sysname; SET @file = 'C:\JOBLOGS\MySQLJobOutputLog.log' -- This is the file outputted from a previous job step. I attach this file to my email (see below) CREATE TABLE #tempfile (line varchar(8000)) EXEC ('BULK INSERT #tempfile FROM "' + @file + '"') IF EXISTS (SELECT * FROM #tempfile WHERE LINE like '%0 rows%') -- LINE is the default column name in tempfile. I'm searching for 0 rows, but you could search for something else BEGIN -- There are 0 affected rows in the MySQLJobOutputLog log file PRINT 'No Rows' END ELSE BEGIN -- There are 1 or more rows in the MySQLJobOutputLog log file /* Choose one of the following to format and send the email - SQL 2005 or earlier */ -- SQL 2005 email using sp_send_dbmail EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Your Profile Name Here', @recipients = 'YourNameHere@DomainNameHere.com', @file_attachments = 'C:\JOBLOGS\MySQLJobOutputLog.log', -- File attached to email @subject = 'Log with error attached' ; END -- Or email using xp_sendmail EXEC master.dbo.xp_sendmail @recipients = 'YourNameHere@DomainNameHere.com', @attachments = 'C:\Joblogs\MySQLJobOutputLog.log', -- File attached to email @subject = 'Log with error attached'; END -- Be sure to drop the temp table DROP TABLE #tempfile