How to Configure Database Mail in SQL SERVER 2005
Introduction
It has been observed that several programmers face difficulty in configuring the database mail in SQL SERVER 2005 or its higher versions. So here, we are sharing some examples where the database mail concept is used and certain steps to configure database mail in the SQL server.
If you have configured the database mail, it gives a notification of an automated job running in SQL SERVER. It informs all the related users about the successful completion of the automated job. For example, when an automated backup of the database is crashed, the user can be notified through mail if Database Mail is configured and set.
What is the Concept of Database Mail?
Using database mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database mail is a mail queuing system. A user will be able to set up multiple accounts and profiles to support different applications need.
The messages are stored in a queue within the MSDB database waiting to be processed. When an email message is placed in the queue, an external process is triggered to send the email messages in the queue to the appropriate mail server. Once the email has been sent an email message with the status of the delivery is then posted back to SQL SERVER. Database mail is the concept of sending mail through SQL SERVER 2005 or its higher versions. Database mail doesn’t require Outlook Express or MAPI (Messaging Application Programming Interface). Database mail is based on SMTP (Simple Mail Transfer Protocol) and is also very fast and reliable.
Four Major Steps for Database Mail Configuration
- Enable database mail.
- Create profiles and accounts.
- Configure E-mail.
- Send a test E-mail.
Step 1:- Enable Database Mail
There are two ways through which a user can enable Database mail.
- Using a procedure for enabling database mail.
- Using the wizard.
The procedure for enabling the Database Mail is as follows.
Open the query window and execute the system procedure to enable the Database Mail feature in SQL Server 2005 and its higher versions.
The system procedure is –
sp_CONFIGURE ‘show advanced’, 1;
GO
RECONFIGURE
GO
sp_CONFIGURE ‘Database Mail XPs’, 1;
GO
RECONFIGURE
GO
If you are using a Wizard follow the steps as follows:
- Open the SQL SERVER 2005 or its higher version software.
- Explore the Management tab
- Right-click on the Database mail tab
- Click on the next button. You will get a screen as below.
Note:
- The first option is selected by default which means the user has to create a new profile and account for database mail.
- If the profile and accounts are already created, then select the second option “Manage Database Mail accounts and profiles”
- The last option is to view and change system parameters.
- Click on the next button. You will get a screen as below.
Step 2:- Create Profile and Accounts
- Enter the Profile name and description of the profile for which the Database Mail is to be configured.
- Then click on the Add button and you will get a screen as below.
Step 3:- Configure Email
- Fill in the text for the Account name and description.
- Enter the E-mail address through which the e-mail is to be sent.
- Enter the display name for the e-mail.
- Enter the Server name.
Note:
- We have used the server name smtp.gmail.com as we are using the Gmail pop3 server which is freely available.
- By default port number is 25. However, the user can use a port number like 587 or 465 for the Gmail pop3 server.
- Then fill in the basic authentication information.
- The username is the e-mail address of the sender.
- Password and confirm password should your e-mail ids password. For example, we have used our Gmail e-mail address so we have entered the password of our Gmail account.
- Then click on the OK button and you will get a screen as below.
- Then click on the NEXT button and you will get a screen as below.
- The user can create more than one profile and select the default profile.
- Then click on the Next button and you will get a screen as below.
- The user is not required to change any system parameter.
- Click the Next button and the user will get a screen as below.
- Click on Finish for the completion of the Database mail configuration.
- The user will get a screen as below.
- The user can see the successful completion of the Database mail configuration.
- Click the close button and send a test e-mail to follow the below steps.
Step 4:- Send a Test Email
Right-click on the Database Mail tab and select the Send Test E-mail.
The user will get a screen as below.
Enter the e-mail address of the recipients in the To field, then click on the Send Test E-mail button. The user will get a screen as below.
The above screen displays the number of e-mails sent.
Click on the OK button to finish the task of Test Mail.
And here, we are done with the configuration of the database in SQL Server. DEV IT has expertise in various areas such as cloud computing, enterprise mobility, enterprise applications, infrastructure management services, custom application development, cloud development and many more.