Database Mail on SQL Server 2005

	How to Set Up Database Mail on SQL Server 2005  Anyone who's ever set up SQL Mail on a previous version of SQL Server will remember how tricky it can be. SQL Server 2005 has simplified things with SQL Server Database Mail. Although it's much easier and more reliable, it's still not obvious how to set it up. In this easy to understand tutorial we'll set up SQL Server Database mail in three simple steps: 1 - Set up the mail account 2 - Set up database mail 3 - Test database mail Let's get started. Step 1. Set Up the Mail Account for SQL Server Database Mail First of all, we will create a new email account. In this example we'll use an account called databasemail@mycompany.com. You can use an existing account to try this out, as no special mail account settings are needed for SQL Server Database Mail. You can even use public mailbox accounts, such as Hotmail or Yahoo! However, keep your personal mail separate from your database mail – it makes life simpler. Once the email account has been set up on the mail server, it’s best to check that it works by testing it in Outlook. This can save a lot of time and frustration when you set up database mail. If you’re using Outlook 2003, just click on the Tools menu, and then select Email Accounts which opens the following window:
	let us see and analyse through fig. see below for more pictures
	Figure 1: Add a new email account in Outlook 2003
	Click on the Add a New Account radio button, then on Next. The window in Figure 2 appears.
	Figure 2: Setting up a test account in Outlook 2003
	Enter the details of the email account you are going to use, including the POP3 and SMTP server names. If you don’t know these, check the details of one of your existing email accounts, or ask your mail administrator or ISP. Finally, make sure that the Remember Password box has a tick in it. Click on the Test Account Settings button to open this window:
	Figure 3: Outlook tests your email account settings
	If you hit problems at this stage, double check: Email address Incoming mail server name Outgoing mail server name User name Password If these are OK and you still have problems, bring up a DOS command box and type ping mail.mycompany.com and ping smtp.mycompany.com (or whatever your own mail administrator advises you to use) If you can't ping both of your mail servers, particularly your SMTP server, you have network connectivity problems which need to be resolved first before going any further. Once you've tested the account successfully, complete the setup of the email account, and then click Send/Receive in Outlook. If everything's working, you should get an email message in your Inbox similar to the one in Figure 4:
	Figure 4: A test email message sent from Outlook
	Step 2. Set up SQL Server Database Mail OK, your mail account is now working. Let's do something more interesting! This is the longest step, but it’s very simple. First, open up SQL Server Management Studio. It doesn't matter if you do this on your own PC or on the server where database mail is going to be set up. Open the Object Explorer and connect to the database server where you want to install database mail. Open up the Management node, right-click on the Database Mail node and select Configure Database Mail, as shown in Figure 5 below:
	Figure 5: Configure database mail in SQL Server Management Studio
	After right-clicking on Configure Database Mail, the Mail Configuration Wizard splash screen will appear. Click on Next, and select Set up Database Mail, as shown below:
	Figure 6: Configure SQL Server Mail with Database Mail Configuration Wizard
	Click on Next to continue. If SQL Server tells you that The Database Mail feature is not available, click on "Yes" to enable it. The next thing you'll see is the New Profile form:
	Figure 7: Database Mail Configuration Wizard - New Profile
	Type a Profile Name of your choice, and an optional description. Click on Add and the window in Figure 8 appears:
	Figure 8: Tell SQL Server Database Mail which SMTP mail account to use to send mail
	Next, click on OK. The database mail account will be added to the mail profile. You’ve probably noticed that several mail accounts can be added to a profile. This is a great idea, because if one of your mail servers crashes, SQL Server can still send database mail. Built in resilience right from the start! 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!