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

,

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

Rate

4.38 (8)

Share

Share

Rate

4.38 (8)