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.
Email SP Results
Will take a stored procedure and email the results in a dynamically generated embedded HTML table or as an attachment.
2016-12-05 (first published: 2015-05-29)