|
|
|
 |
| How to Set Up Database Mail on SQL Server 2005 - Part 2 of 2 |
|
 |
About the Author
With security clearance and well over a decade of experience with SQL Server, Web Monkey is the resident DBA at
Norb Technologies and a real database guru.
A regular contributor to SQL Server Club, his background includes software development, hardware, a
smattering of essential networking knowledge and an unhealthy interest in web security. He has an obsession with
automating repetitive tasks, but has a tendency to forget where he left his last open bag of fruit and nut...
 |
|
|
|
|
Welcome back...
Click on the Next button again, and the screen in Figure 9 will appear:
|
 |
| Figure 9: Manage Profile Security on Database Mail Configuration Wizard |
| For the sake of this article, we’ll make the profile a public one by ticking in the check box in the
Public column. You might decide to make the profile private later, but let's get things working first.
Click on Next again and the following window appears: |
 |
| Figure 10: Configure System Parameters |
| We’ll use the default configuration parameters as they work fine in most cases. If you have intermittent
problems, you might want to alter the settings on this page. Alter the Retry Attempts and Retry Delay if you have an unreliable connection or heavily loaded mail server.
Click on Next again and the summary screen shown in Figure 11 will appear. |
 |
| Figure 11: Database Mail Configuration Wizard Setup Complete |
| Click on Finish and you should see the Configuration window shown as below: |
 |
| Figure 12: The Final Configuration Screen |
| Click on Close. Congratulations! You’ve set up your first SQL Server Database Mail service.
Now you just need to test it...
Step 3. Test SQL Server Database Mail
Testing SQL Server Database Mail is simple. Open SQL Server Management Studio, right click on the Database Mail node, and click on Send Test Email.
The window shown below in Figure 13 will appear:
|
 |
| Figure 13: Sending a Test Email with Database Mail |
| At this point, one of two things can happen. If Database Mail has been enabled, then the confirmation window in Figure 14
will be seen, confirming that the email was sent successfully: |
 |
| Figure 14: Database Mail Test Email |
| However, if Database Mail has not been enabled for the SQL Server, the error message in Figure 15 will appear immediately: |
 |
| Figure 15: SQL Server blocked access to procedure 'dbo.sp_send_dbmail'... |
| Don’t panic! This happens because Microsoft has tied down many out-of-the-box settings in SQL Server to make it secure.
Click on OK, but leave the Send a Test Email window open while you enable the database mail settings – it takes just a few seconds.
To enable database mail, simply click on the following from your Windows Start menu:
Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration
The window in Figure 16 will appear: |
 |
| Figure 16: SQL Server 2005 Surface Area Configuration |
| Click on the hyperlink at the bottom, Surface Area Configuration for Features to open the following configuration window: |
 |
| Figure 17: Configuring Individual Features to Enable Database Mail |
| Simple click on the checkbox to Enable Database Mail stored procedures, then click on OK.
You can close the Surface Area Configuration screen at this point.
Now switch back to the Send a Test Email window and try again. If everything's working at the SQL Server end, you should see the window
in Figure 14.
At this point you should check the mailbox of the email account where you sent the test email (in our case, bob.evans@mycompany.com), and you should find that
you’ve received an email similar to the following: |
 |
| Figure 18: Successful Database Mail |
| Well done - you've successfully set up database mail on SQL Server! |
|
 |
 |
If you liked this article please join SQL Server Club to receive our newsletter. Membership is free and you'll be first to hear about
new articles, performance tuning tips written by practising DBAs, industry news, blogs, interviews and lots more.
|
 |
|
 |
|
|
|
|
|
| Part 1 || Top of Page |
