How to: Send email with attachment from SQL Server 2008 Enterprise Edition

Some time back, I had a problem with exporting huge amount of data as csv to view in excel from a production server. It took several minutes when the server load was normal and far worse when it experienced peak traffic. My client asked me if I could take this feature off of the live server and then automate this process so that he would receive the exported data in the mail instead of requesting data from me. This meant three things, creating a sql job that executed once a week which executed an export script using 'bcp' feature to a file which would then be sent as an attachment to the client. I will explain how to configure SMPT mail server and send email in SQL Server 2008 Enterprise Edition as a two part series.

This is the second of the two part series and here I would like to show how to send email with attachment from SQL Server 2008 Enterprise Edition. If you'd like to follow the first part, here is the link http://dreamfusions.blogspot.com/2010/02/how-to-configure-smtp-mail-server-in.html.

• Open SQL Server Management Studio (SSMS) and login either using your Windows Authentication or user credentials.


• Once there, if you don't already see the "Object Explorer" hit F8 to open it.


• Expand the "Management" folder and right click on "Database Mail".


• Select "Configure Database Mail".


• You will need to first create a new profile. To do this, select the first radio option that reads "Set up Database Mail by performing the following tasks."


• Give a Profile Name and a short description. The Profile Name is important to send emails.


• Then click on "Add" button to add SMTP server account you configured in part I of this series.


• Fill out the necessary items. Leave the SMPT port to 25. Enter 127.0.0.1 as your server name. Also if you have Windows Authentication, select that or enter the login you used earlier.


• Now you are done with profile and mail server account.


• You can now test by right clicking on Database Mail and clicking on Send Test email.


• To verify use:SELECT * FROM sysmail_sentitems --to view sent items

SELECT * FROM sysmail_faileditems --to view failed items
SELECT * FROM sysmail_log --to view the reason why your mail was not sent among other things.

• Now to manually send email (this is our goal), you need to first reconfigure the Database Mail to enable it. To do this run the following scripts.

sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
sp_CONFIGURE 'Database Mail XPs' 1
GO
RECONFIGURE

• You are now ready to send email manually!! The sample script sends email with attachment. You use the msdb database and the profile you created should be entered.

[USE msdb]
GO
EXEC sp_send_dbmail
@profile_name='myMailProfile',
@recipients='tej.rana@hotmail.com',
@subject='Sending message from SQL Server 2008',
@body='You have received mail from SQL Server',
@file_attachments ='c:\Reports\report.csv'


That's all there is to it.