May 14, 2009 at 3:59 am
Hi,
I need to run a procedure that will check a table for certain combinations of values, and if it finds any rows that fail this validation I need it to notify someone, along with data that they need to locate these anomalies.
I have scheduled SQL tasks in the past, but I've never sent emails if the result of the task is something I didn't want. I also can't find out how to send data generated from the SP to someone (preferably by email).
In addition, our customers run SS 2000 or 2005, so I need to know how to do it for both versions.
Thanks for any assistance.
BrainDonor.
May 14, 2009 at 8:51 am
You can send an email from a job step by using xp_sendmail (SQL 2000) or sp_send_dbmail (SQL 2005). In both, use the @query argument to specify the stored procedure. You can either send the results in the body of the email or as a file attachment. See "xp_sendmail" in 2000 BOL and "sp_send_dbmail" in 2005 BOL for details.
Greg
May 15, 2009 at 4:50 am
Hi Greg,
Thanks for that. I've spent the day looking through the BOL and various other resources and have come to the conclusion that they weren't suitable. Not all of our customers have Email software installed on their SQL Servers, and I understand that that has to be the case.
However, as a result of these investigations I came across xp_smtp_sendmail and this appears to work quite easily as far as my limited testing has shown.
Thanks for your assistance.
BrainDonor.
May 15, 2009 at 11:14 am
Yes, that's useful for SQL 2000. You don't need it in SQL 2005 because Database Mail uses SMTP and doens't require a mail client.
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply