Here’s a very quick guide to configure database mail for the first time (This should also work in MSSQL 2008/R2). You’ll need these beforehand:
Valid email account
IP address or host name of your mail server
Mail servers SMTP port
SQL Server Management Studio
And here’s how to configure database mail (using SQL Server Management Studio):
1. Run the following SQL script to enable database mail
sp_CONFIGURE ‘show advanced’, 1
sp_CONFIGURE ‘Database Mail XPs’, 1
You should get the following in the query results window
Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Database Mail XPs’ changed from 0 to 1. Run the RECONFIGURE statement to install.
2. In Object Explorer, expand Management, right click on Database Mail and click Configure Database Mail
3. Click next if the Welcome dialog screen appears, then select Set up Database Mail by performing the following tasks and click Next
4. Enter a name and optionally a description for the Profile. This will have the email account associated with it.
5. Click Add… This will open a dialog prompting for the email account and server settings, enter them as required and select the relevant SMTP Authentication method (I tend to use Anonymous). Click OK to close the dialog
6. This is all you need to get db mail up and running. Click Next on the following dialog boxes, confirming the configuration and then click Finish – you can optionally configure public profiles etc in these preceding dialog boxes
7. Final step is to send a test mail, in Object Explorer right click Database Mail under Management and click Send Test E-Mail… Enter the recipients address in the To: field and click Send Test E-Mail
Your db mail profile can have multiple email accounts associated with it, if the first account in the list fails to send it will move to the second, third etc.