Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Topaz
Posting Yak Master
199 Posts |
 Posted - 2011-02-04 : 06:33:58
|
| Is it possible to have my SQL server run a query every single day and email me the results?For example if I wanted this query running daily: select website from wce_contact The only bit of info that I would require in the email is the number of records it produces.Is something like this possible?JT |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-04 : 07:08:28
|
| Yes, you need to enable database mail on the server (here's how: http://msdn.microsoft.com/en-us/library/ms175951.aspx). Then use the system stored procedure sp_send_mail (http://msdn.microsoft.com/en-us/library/ms190307.aspx) and specify the @query-parameter to be "select count(website) as websites from wce_contact".- LumbagoMy blog-> www.thefirstsql.com |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-04 : 07:09:06
|
| ...and the create a new job using sql server agent that will run this procedure as often as you want.- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
Topaz
Posting Yak Master
199 Posts |
Posted - 2011-02-04 : 07:25:02
|
| I've enabled mail on my database server and have been able to send a test email (no problem)Created a job that alerts me when the job is complete. I'm now just having problems with getting the email to send me the results.How do I edit the system stored procedure sp_send_mail?JT |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-04 : 08:49:39
|
| You don't edit the procedure, just send your query as a parameter to it:EXEC sp_send_mail @..., @query = 'select ...'- LumbagoMy blog-> www.thefirstsql.com |
|
|
|
|
|
|
Auto emailing reports