This is a quick how-to for setting up Database Mail. The first thing we need to do is to enable Database Mail, which due to security risks, is disabled by default. There are three ways to enable Database Mail.
Running sp_configure
The Database Mail wizard
SQL Server Surface Area Configuration
We will use the Surface Area Configuration. When you open the Surface Area you should the image below.
We want to select the Surface Area Configuration for Features option. This will bring up another window. We want to drop down our SQL Server, drop down Database Engine, and click on Database Mail.
Check the Enable Database Mail stored procedures box and click OK. We have now enabled Database Mail, leaving us the task of configuring. Open up Management Studio and connect to your database server. In Object Explorer, expand out the Management Node and right click on Database Mail.
We want to check Set up Database Mail by performing the following tasks: and then click Next. Enter a Profile name and click add. This will bring up the New Database Mail Account window. Enter the relevant information (this will vary person to person) and click OK and then Next. The Manage Profile Security window should now be up. Select the profile you created and click Next.
Note: For this article we are going to select Public Profile but in real world use I would recommend using Private Profiles as they offer more security.
Configure System Parameters is the next item. This is where we can block file extensions, set up maximum file size, and other options for our mail system. After you have modified these to your liking, or not at all, click Next. Click Finish and you will see the setup process create everything. All actions should come out success. If there is an error, please go back through the steps and verify all the information entered is correct.
All that is left is to send a test e-mail:
@profile_name should be the name of the profile you created or the one you want to use in the case of multiple profiles.
@body is the body of you e-Mail
@recipients are the e-Mail addresses that your message is will be delivered to
msdb..sp_send_dbmail @profile_name = 'Test_Mail', @body= 'testing',@recipients = 'recipients@myrecipients.com'
The following link list out the options for sp_send_dbMail.
http://technet.microsoft.com/en-us/library/ms190307.aspx
Author: Reuben Shaffer
Date: 07.01.2008