How to Configure Database Mail in SQL SERVER 2005

Database Configuration in SQL Server

Introduction

It has been observed that several programmers face the 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 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 account 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 also very fast and reliable.

Four major steps for Database mail configuration.

  1. Enable database mail.
  2. Create profile and accounts.
  3. Configure E-mail.
  4. Send a test E-mail.

Step 1:- Enable Database Mail

There are two ways through which a user can enable Database mail.

  1. Using a procedure for enabling database mail.
  2. Using the wizard.

Procedure for enabling the Database Mail is as follows.
Open the query window and execute the system procedure to enable Database Mail feature in SQL Server 2005 and its higher versions.

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

How to Configure Database Mail in SQL SERVER 2005

Click on the next button. You will get a screen as below.

How to Configure Database Mail in SQL SERVER 2005

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”
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

How to Configure Database Mail in SQL SERVER 2005

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

How to Configure Database Mail in SQL SERVER 2005

Fill the text for Account name and description.
Enter the E-mail address through which the e-mail is to send.
Enter the display name for the e-mail.
Enter the Server name.

Note
We have used server name as smtp.gmail.com as we are using the Gmail pop3 server which is freely available.
By default port number is 25. But the user can use the port number like 587 or 465 for Gmail pop3 server.
Then fill the basic authentication information.
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.

Database Configuration in SQL Server

Then click on the NEXT button and you will get a screen as below.

Database Configuration in SQL Server

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.

Database Configuration in SQL Server

The user does not require to change any system parameter.
Click Next button and user will get a screen as below.

Database Configuration in SQL Server

Click on Finish for completion of Database mail configuration.
The user will get a screen as below.

Database Configuration in SQL Server

 

The user can see the successful completion of the Database mail configuration.
Click close button and to send a test e-mail to follow below steps.

Step 4:- Send a test email

Right Click on the Database Mail tab and select the Send Test E-mail.

Database Configuration in SQL Server

The user will get a screen as below.

Database Configuration into SQL Server

Enter the e-mail address of the recipients in To field, then click on the Send Test E-mail button. The user will get a screen as below.

Database configuration in SQL Server

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 Information Technology Limited (DEV IT) is a public listed company on NSE Emerge: [DEV IT] and is an end-to-end IT service provider that believes in offering innovative solutions. DEV IT has its expertise in various areas such as cloud computing, enterprise mobility, enterprise applications, infrastructure management services, custom application development, cloud development and many more.

The following two tabs change content below.
Yatin Parmar
I am a team lead (.NET) at Dev Information Technology Limited. I like to explore database technology.
Yatin Parmar

Latest posts by Yatin Parmar (see all)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>