jump to navigation

Configuring Database Mail in SQL Server May 11, 2012

Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008.
Tags:
trackback

In this email I will demonstrate in detail how to set up Database Mail.I am sure that this feature is well documented  elsewhere in the web but I decided to give it a go.

Database Mail is a very useful feature.Both the Maintenance Plan Wizard and Maintenance Plan Designer have the ability to send e-mails to DBAs.

We can use the SQL Server Management Studio to set up Database Mail.

We need an instance of SQL Server running in our machine. I have SQL Server 2008 R2 Developer edition installed in my machine.

In case you do not have an instance of Enterprise edition or Developer edition  of SQL Server 2008 / R2, you can download it here . This is a trial version.

1) Launch SQL Server Management studio and connect to the desired instance of the SQL Server.

2) Navigate to the Management folder , right-click on Database Mail and select Configure Database Mail.
You will see the Splash screen and after you click Next , the wizard starts off with the Select Configuration Task

Ηave a look at the picture below

3) We select the option Set up Database Mail by performing the following tasks.Click on Next to continue

4)If Database Mail has not been enabled you will see a message like the one in the picture below.Click Yes

5) If you are interested on how to turn on Database Mail in SQL Server you can type the following T-SQL statement in a new query window


sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

6) In the next screen you will be prompted to create a Database Mail Profile.

Have a look at the picture below

A profile is basically a collection of SMTP accounts that can be used by SQL Server to send messages.When SQL Server sends a message it is sent to the profile and then the profile is responsible for seeing that the e-mail has been delivered.

We must enter a profile name,an optional description and then we add and configure an SMTP account.

Ι add in the Profile name : SQL Server Admin

I add in the Description : This is a SQL Server Admin profile

Then click the Add button.The New Database Mail Account screen appears

Have a look at the picture below to see what one might enter in the available fields.

Ι use Basic Authentication which requires to provide a user name and a password. Then I click OK.You will return to the original mail profile screen.

7) We now need to continue with the Database Mail Configuration Wizard.Click on Next to reach the Manage Profile Security screen.

Now we must assign a profile as public as private.Only specific roles or users have access to private profiles.A public profile allows any user or role with access to send mail.I will keep things simple and I will create a public profile.Then click Next to move on to the Configure System Parameters screen

Have a look at the picture below

Then click Next on the wizard and then we move to the summary screen.We click Finish to close the Wizard.

Have a look at the picture below

Click Close.The profile and the SMTP account has been created

8)Now we need to check if Database Mail works correctly.We can send a test email.Select the Database Mail folder, right-click and select Send Test E-mail

Ηave a look at the picture below

In the To field  enter an email address and then click Send Test E-mail.It worked fine in my case and the e-mail was successfully sent.

You can use T-SQL to send an email. In a new query window type


USE msdb
GO
EXEC sp_send_dbmail @profile_name='SQL Server Admin',
@recipients='nkantzelis@q-training.gr',
@subject='Hello Admin!!!! Test message',
@body='This is a test message.
Database Mail works!!!!'

9) You can find information about sent emails,failed emails and more by typing the following T-SQL statements in a query window.


SELECT *
FROM sysmail_mailitems
GO

SELECT *
FROM sysmail_sentitems
GO

SELECT *
FROM sysmail_faileditems
GO

SELECT *
FROM sysmail_log
GO

Hope it helps!!!!!

Comments»

1. Dot Net Rules : Configuring Database Mail in SQL Server - May 11, 2012

[…] to send e-mails to DBAs.We can use the SQL Server Management Studio to set up Database Mail. (read more) Share Posted: Παρασκευή, 11 Μαΐου 2012 2:28 πμ από το μέλος […]

2. Automating SQL Server Administration Tasks « DOT NET RULES - May 23, 2012

[…] also have notifications of one or more operators by e-mail using Database Mail. Have a look in this post of mine to see how to configure Database Mail in SQL […]

3. kiquenet - December 26, 2012

How can I create profile programatically using sql script ?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: