By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Best way to send email from SQL

P: 3
Hello,

I have a SQL script that grabs the status of reports that are run of the ReportServer database (Reporting Services 2000). I would like to run this script weekly and have it email the results to me. (The script basically checks the status of all the subscription reports.) The issue we have had is that, if a report subscription fails, there is no notification to the Admin (other than the admin has to weekly check all the subscriptions or run this SQL script). I would like to have the SQL script run each week and email the results to myself. Would the best way to do this be creating a Job?

Thanks,
Jennifer
Apr 20 '09 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
Hi Jennifer
Do you have SQL Mail working?
If so, you can use the xp_sendmail stored proc.

Expand|Select|Wrap|Line Numbers
  1. EXEC master..xp_sendmail 
  2.    @recipients = 'TheEmailAddress ; AnotherEmailAddress',
  3.    @message = 'The emails body text',
  4.    @copy_recipients = 'TheEmailAddress ; AnotherEmailAddress',
  5.    @subject = 'The emails Subject Text'
  6.  
You can put that into a dts and schedule a job.
You can execute queries in the dts to get the addresses from a table.
There are lots of possibilities

To test, just copy the above.
Paste it into query analyser.
Fill in your email address and then execute it.
Hopefully , you will receive an email


execute
Expand|Select|Wrap|Line Numbers
  1. master..xp_readmail
  2.  
to get a list of the emails in the SQLServer mailbox.
Apr 20 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.