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 |
|
exibar
Starting Member
3 Posts |
 Posted - 2004-06-17 : 16:31:59
|
| I give up, I've searched and searched and the closest I can find to sending the results of a query is the following code:declare @Body varchar(4000),@results varchar(4000)set @body = 'select * from diary3 where id=2' EXEC @Results = sp_sqlexec @Body exec sp_send_cdosysmail 'username@here.com,'username@there.com','Did this work????',@results This does indeed send an e-mail to me (when I put my e-mail address in the proper fields) but the body of that e-mail is always just a zero. What I really really need is to send the results of the select statement in an e-mail on a scheduled basis. What is happening is the query is running, but it displays the results in enterprise manager, and e-mails me a message with a ZERO in the body. Can anyone please help me? I've spent too many hours on finding a solution to this. I don't mind using DTS if I have to, nor do I mind using a regular SQL query to send the results. Anythign that will work I'll be happy with. Please provide working code examples as well please, code examples are worth 100,000 words when it comes to this for me. thanks all! Mike B |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-17 : 16:34:13
|
| Displays the result in Enterprise Manager???So what happens when you run the code in Query Analyzer?Tara |
 |
|
|
exibar
Starting Member
3 Posts |
Posted - 2004-06-17 : 16:40:43
|
| oops, sorry about that, it's been a long day :-( The results are shown in Query Analyzer, not Enterprise manager. Just the same as if I had run just the "select * from Diary3 where id=2" I would have figured that the results of that query would have been e-mailed, not a "0". I'm so puzzled by this :-( thank you for the help! Mike B |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-17 : 16:51:46
|
| sp_send_cdosysmail does not allow a query in @body. You could use a variation of it though:http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649You would have to save the results of the query to a file, then attach the file to the e-mail.Tara |
|
|
|
exibar
Starting Member
3 Posts |
Posted - 2004-06-17 : 16:59:35
|
| Thank you very much for the quick help Tara. I'll give Jaspar's code a try. Is there any method that will allow me to send the output of a query in an e-mail? I'm game for just about anything at this point. But, if I have to, I'll just send an attachment. thanks again Tara! Mike B |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
sending the output of a SQL query using CDOSYS