How to send mail from SQL server?

Question

Hi!

I started to use SQL for a few days, so i'm just a beginner. I want to do the following: I want to run a query in everey month, which gives back the data i need, then store it in an .xls file or something else, and then send it to some recipients. Can i do this fully automatically somehow in SQL Server 2005? Could someone give me an example or a guide how to do this? I'm using Microsoft SQL Server Management Studio 2008.

Thanks, kampi

Answer 1

You can create a SQL Server Agent job that executes the sp_send_dbmail stored procedure.

The results of the query can be included in the body of the e-mail message or attached as a file.

Answer 2

What you are looking for is SQL Server Reporting Services and its subscriptions. They allow you to automatically send reports (results from queries ran against your DB) via email (in a PDF, HTML, XLS, etc. formats).

Answer 3

You can use the below code snippet to send a mail from the SQL server.

EXEC master..xp_sendmail @recipients = '[email protected]', @subject = 'Hello from SQL team', @message = 'A new mail has been sent.'

Also more information is found on these procedures in the below link. http://msdn.microsoft.com/en-us/library/aa155737(office.10).aspx

Answer 4

refer these links:

http://msdn.microsoft.com/en-us/library/ms190307.aspx
http://msdn.microsoft.com/en-us/library/ms189505.aspx