Send query results via email?

[octavian10]

How dificult is it to send the results of a query via email? I run a simple SELECT stored procedure weekly and now need to send these results via email to one recepiant. I have access to both SQL Server 2000 and 2005. I am not using Exchange Server and do not have Outlook installed on any of these SQL Servers. These two things have come up often in my reaserch on this subject so far, so I thought I should mention them. I am no expert on SQL but this scenerio is coming up more often and I would like to learn how to do it. If someone could explain it to me (with example) I would very much appreciate it. If this setup is not for the faint of hart just let me know and I will buy a book to learn it. Thanks a lot.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[bigfoot]

Hey octavian10, I'm not sure what needs to be loaded on the server as far as mail but I'll ask if you'd like.

We do this all the time here.
Look up the requirements for xp_sendmail.

I create the stored procedure that I want to return the results with. This could be a simple query but I like to use print statements and line things up a bit.
I try not to use cursors but it's all good.

Then I set up a running job in SQL Server Agent, under jobs.

Here is my job.

USE master
Declare @msg varchar(255)
declare @CR1 varchar(5)
set @CR1 = CHAR(10) + CHAR(13)
set @msg = 'Please review the following:' + @CR1
BEGIN
EXEC xp_sendmail @recipients = 'me@mycompany.com; someone@mycompany.com',  
   @query = 'mydatabase.dbo.jjsp_MySalesProcedure',
   @subject = 'The subject of the email',
   @message = @msg,
   @width = 250
END

Now you have a job that you can schedule at any time in SQL Server.


In the stored procedure:

CREATE PROCEDURE jjsp_MySalesProcedure
 @warehouse Varchar(10) = '0101'
AS

PRINT ' Report attached:'
PRINT '  		My Sales Report Report:'
PRINT '		-------------------------------------'
PRINT '		  Report Date: ' + CONVERT(char(30), CURRENT_TIMESTAMP)
PRINT '		  --------------------------------'
PRINT ' '
PRINT '-->> Sales Numbers:'
SELECT (blah, blah)
(this will produce the actual data)

PRINT ' '
PRINT 'End Of Report'

I always put end of report so they know they have all the data.

I hope this helps. If you need any more information, just ask.

 

[bigfoot]

I was looking for an answer for my question and I found this right here in FAQ's.
Enjoy

http://www.tek-tips.com/faqs.cfm?fid=3146

 

[octavian10]

Thanks for all the info, I like the print option but if I just run a SELECT sp with the @query command will the results of that query be visible in the email? Also I will try all of this when I get behind the test machines later today or tomorrow and will let you know. Once again thanks.

"I hear and I forget. I see and I remember. I do and I understand."
- Confucius (551 BC - 479)

 

[bigfoot]

Yes, in fact I'm doing that as I read this. LOL

EXEC xp_sendmail
	@recipients = 'myself@mycompany.com',
	@subject = 'Items Added to the Item Master',
	@message = 'Here are the items that were added to the Item Master Database today.',
	@query = 'SELECT 
	       		Import_Temp.upc,
			Import_Temp.item_no,
			Import_Temp.source,
			Import_Temp.alt_upc,
			Import_Temp.bar_code,
			Import_Temp.global_trade_id,
			Import_Temp.description,
			GetDate()
		FROM import_temp
		LEFT JOIN general_info
			ON 
			import_temp.upc = general_info.upc
			AND
			import_temp.item_no = general_info.item_no
			AND
			import_temp.source = general_info.rec_source
		WHERE 
			general_info.item_no IS NULL
			OR
			general_info.upc IS NULL
			OR
			general_info.rec_source IS NULL'

I did find that I had to add exec master.dbo.xp_sendmail and then also add in the db names in the same way to my tables in the query.

Good luck to you. It's pretty cool.