Configuring Database Mail in SQL

Server

Kareem.S, 30 Dec 2013 CPOL

116K

51

4.69 (23 votes)

Rate this:
vote 1vote 2vote 3vote 4vote 5
This is an enterprise solution for sending mails from the SQL Server database
engine to SMTP servers.

Introduction
This is an enterprise solution for sending mails from the SQL Server database
engine to SMTP servers. SQL Server database applications can communicate with
users through an email system. It provides features like scalability, security, and
reliability.
It uses an SMTP server to send mail. SQL Server 2000 supports SQL Mail, which
supports MAPI profiles to send email instead of an SMTP server. SQL Mail requires a
MAPI-compliant mail server (Microsoft Exchange Server) and a MAPI client
(Microsoft Outlook).
We can send a text message, query result, file as attachment. The database mail
can be used to notify users or administrators regarding events raised in SQL
Server. For example, if an automation process like replication, database mirroring
fails or there are latency related problems then SQL Server can use this feature to
notify the administrators or operators.

Points to Remember

Like SQL Mail, database mail doesnt require a MAPI a compliant mail
server like Outlook Express or extended programming interface.
Better performance. Impact of sending mails to SMTP servers by SQL Server
is reduced as this task is implemented by an external process initiated by
the DatabaseMail.exe file.
Works fine in a cluster based environment.

64-bit support.
Database mail configuration information is maintained in
an MSDB database.
Only members of SysAdmin and DatabaseMailUserRole database role of
MSDB can send mails by default.
Allows sending messages in different formats like text and HTML.
Supports logging and auditing features through different system tables of
MSDB.
The main components of database mail are:

Sp_send_dbmail
This is a system defined stored procedure which is used by SQL Server to send
email using the database mail feature. This stored procedure is present in the
MSDB database.

MSDB Database
Consists of all stored procedures, system tables, and database roles related to
database mail.

Service Broker
To establish communication between the SQL Server engine and the database mail
engine we need a service broker. It submits the messages to the mail engine.

DatabaseMail.exe
This file is present in the Binn folder of the respective instance. It is the database
mail engine.

Figure 1 (Source: BOL) Database Mail Architecture

How it works?
When a run time error occurs due to any automated task like backups,
replication etc database engine raise the error and same information is
submitted to Database Mail engine, then database mail engine will
submit the mail to SMTP Server using EmailID and Password mentioned in
profile. At the last SMTP Server sends mail to recipients.
Error --> DB Engine --> DB Mail Engine --> SMTP Server --> Recipients

FAQ: How to enable a Service Broker in MSDB?

Hide Copy Code

USE [master]
GO
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
GO

MSDB tables related to Database Mail

1.
2.
3.
4.

sysmail_profile: Consists of all the profiles information.

sysmail_account: Consists of SMTP server accounts information.
Sysmail_server: Consists of SMTP server details.
Sysmail_allitems: Mail sent status. If the sent_status is 1 then success,
otherwise failed.

5.
6.

Sysmail_log: To check the errors raised by Database Mail feature.

Sysmail_configuration: Consists of system parameter details.

Steps to configure
1.

Enable the db mail feature at server level

.
Hide Copy Code

sp_configure 'Database Mail XPs',1

reconfigure

2.

Enable service broker in the MSDB database.

Hide Copy Code

USE [master]
GO
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
GO

3.
4.
5.

Configure mail profile (profile is a collection of accounts).

Add SMTP account(s).
Make the profile private or public.
Private profile can be used by:

o
o
6.
7.

sysadmin members and

databasemailuserrole members of MSDB
Set parameters.
Send the mail.

Example: Configuring Database Mail

1.
2.
3.

Go to Object Explorer
Management
Right click on Database Mail and select Configure Database Mail as follows

4.
5.

Next
Select Setup Database Mail by performing the following tasks as follows

6.
7.

Next
Enter profile name = SQL Profile and description as follows

8.
o
o
o
o

Click on Add button and enter the following details. Always use your own
email ID. Generally we have to use here the company email id. We have to raise a
ticket to the mail server admin team to get the following details.
Separate email id for SQL Server (This is From Email ID)
SMTP server name
Port number
SSL feature should be enable or disable.
Here I am using my personal email id. In Basic Authentication option enter the
same email ID along with the valid password of the email ID.

9.
10.
11.

OK
Next
Under Manage Profile Security option make the profile as public by selecting
checkbox and default as follows

12.
13.

Next
Accept the default settings for System Parameters as follows

14.
15.

Next
Finish

16.

Close.

Observations
Hide Copy Code

Use msdb
Go
--Step1: Varifying the new profile
select * from sysmail_profile
--Step2: Verifying accounts
select * from sysmail_account
--Step3: To check the accounts of a profile
select * from sysmail_profileaccount
where profile_id=3
--Step4: To display mail server details
select * from sysmail_server

17.

We have configured database mail feature successfully. Let's test the mail
feature as follows.
18.
Go to Object Explorer -> Management -> right click on Database Mail ->
Send Test Email

19.
o
o
o
o

20.
21.

Enter the following details. You can use required values.

Select Profile name: SQLProfile
To: [email protected] (Any Email ID- generally it should be
administrators group email ID)
Subject: Hi, Backup Alert
Body: Backup of master database was generated successfully.

Send Test Email -> OK

Verifying whether the mail was send successfully or not. Take new query and
run the following command and check sent_status column value for your mail as
follows

22.

Check your mail box you can find new Email from SQL Server.

Sending Mail using SP_SEND_DBMAIL

We can send the mail programmatically from any stored procedure, job or batch
file using sp_send_dbmail system procedure. Before sending the mail we can check
the required parameters by viewing the definition of stored procedure.

We have to pass the following parameters to the above stored procedure.

Profile_name (We can mention the above profile name which we have
created)
Recipients (We can mention multiple recipients by separating with

src="http://www.codeproject.com/script/Forums/Images/smiley_wink.gif" />
Subject
Body

"

Example 1: Sending mail using the sp_send_dbmail stored

procedure
Step 1:

Step2: Verifying using sysmail_allitems. Here check the sent_status column of last
Email.
Hide Copy Code

use msdb
go
select * from sysmail_allitems

Example 2: Sending mail using sp_send_dbmail stored

procedure which has query result as message.
Step 1: Sending mail (I am using emp table present in Test database)
Hide Copy Code

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = '[email protected]',
@query = 'SELECT COUNT(*) as No_Empls FROM
Test.dbo.emp',
@subject = 'No of Employees Working';

Step 2: Verifying using sysmail_allitems. Here check the sent_status column of last
Email.
Hide Copy Code

use msdb
go
select * from sysmail_allitems

Configuring Database Mail Using T-SQL

Script
Hide Copy Code

--Enabling Database Mail

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'Database Mail XPs',1
reconfigure
Hide Copy Code

--Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLProfile',
@description = 'Mail Service for SQL Server' ;
Hide Copy Code

-- Create a Mail account for gmail. We have to use our company mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQL_Email_Account',
@email_address = '[email protected]',
@mailserver_name = 'smtp.gmail.com',
@port=587,
@enable_ssl=1,
@username='youremail',
@password='Emailid password'
Hide Copy Code

-- Adding the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLProfile',
@account_name = 'SQL_Email_Account',
@sequence_number =1 ;
Hide Copy Code

-- Granting access to the profile to the DatabaseMailUserRole of MSDB

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQLProfile',
@principal_id = 0,
@is_default = 1 ;
Hide Copy Code

--Sending Test Mail

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = '[email protected]',
@body = 'Database Mail Testing...',
@subject = 'Databas Mail from SQL Server';
Hide Copy Code

--Verifying, check status column

select * from sysmail_allitems

Summary

Database Mail feature was introduced in SQL Server 2005 version, which can be
used to notify the administrators/operators. It provides better performance as well
as cluster aware feature.

MI Script de Configuracin
--Enabling Database Mail
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'Database Mail XPs',1
reconfigure
--Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLProfile',
@description = 'Mail Service for SQL Server' ;
-- Create a Mail account for gmail. We have to use our company mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Caja_Cristal',
@email_address = '[email protected]',
@mailserver_name = 'smtp.gmail.com',
@port=587,
@enable_ssl=1,
@username='[email protected]',
@password='ccristalscl'
-- Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLProfile',
@account_name = 'Caja Cristal',
@sequence_number =1 ;
-- Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQLProfile',
@principal_id = 0,
@is_default = 1 ;
--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = '[email protected]',
@body = 'Database Mail Testing...',
@subject = 'Databas Mail from SQL Server';
--Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLProfile',
@recipients = '[email protected]',
@body = 'Database Mail Testing...',
@subject = 'Databas Mail from SQL Server';

--Verifying, check status column

select * from msdb.dbo.sysmail_allitems

-- Email Querry-DECLARE @Body varchar(max)

declare @TableHead varchar(max)
declare @TableTail varchar(max)
declare @mailitem_id as int
declare @statusMsg as varchar(max)
declare @Error as varchar(max)
declare @Note as varchar(max)
Set NoCount On;
set @mailitem_id = null
set @statusMsg = null
set @Error = null
set @Note = null
Set @TableTail = '</table></body></html>';
--HTML layout-Set @TableHead = '<html><head>' +
'<H1 style="color: #000000">HEADER OF TABLE</H1>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;fontsize:9pt;color:Black;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#F6AC5D>'+
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td></tr>';
--Select information for the Report-Select @Body= (Select
Column As [TD],
Column As [TD],
Column As [TD],
Column As [TD],
Column As [TD]
FROM [DB].[dbo].[Table]
where -condition(whatever you want to do else ...)
For XML raw('tr'), Elements)

-- Replace the entity codes and row numbers

Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')

Set @Body = @TableHead + @Body + @TableTail

-- return output-Select @Body
--Email
EXEC msdb.dbo.sp_send_dbmail
@profile_name =email', <--This is the mail account to sent from.
@mailitem_id = @mailitem_id out,
@recipients='[email protected]',
@subject = 'subject Email',
@body = @Body,
@body_format = 'HTML';

Why not using CSS?

Example:
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
SET @subject = 'Query Results in HTML with CSS'
SET @tableHTML =
N'<style type="text/css">
#box-table
{
font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;
font-size: 12px;
text-align: center;
border-collapse: collapse;
border-top: 7px solid #9baff1;
border-bottom: 7px solid #9baff1;
}
#box-table th
{
font-size: 13px;
font-weight: normal;
background: #b9c9fe;
border-right: 2px solid #9baff1;
border-left: 2px solid #9baff1;
border-bottom: 2px solid #9baff1;
color: #039;
}
#box-table td
{
border-right: 1px solid #aabcfe;

border-left: 1px solid #aabcfe;

border-bottom: 1px solid #aabcfe;
color: #669;
}
tr:nth-child(odd) { background-color:#eee; }
tr:nth-child(even) { background-color:#fff; }
</style>'+
N'<H3><font color="Red">All Rows From [AdventureWorks].[Sales].[SpecialOffer]</H3>' +
N'<table id="box-table" >' +
N'<tr><font color="Green"><th>SpecialOfferID</th>
<th>Description</th>
<th>Type</th>
<th>Category</th>
<th>StartDate</th>
<th>EndDate</th>
</tr>' +
CAST ( (
SELECT td = CAST([SpecialOfferID] AS VARCHAR(100)),'',
td = [Description],'',
td = [Type],'',
td = [Category] ,'',
td = CONVERT(VARCHAR(30),[StartDate],120) ,'',
td = CONVERT(VARCHAR(30),[EndDate],120)
FROM [AdventureWorks].[Sales].[SpecialOffer]
ORDER BY [SpecialOfferID]
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'
EXEC msdb.dbo.sp_send_dbmail @recipients='[email protected]',
@subject = @subject,
@body = @tableHTML,
@body_format = 'HTML' ;

ENVIAR IMAGEN
declare @body1 varchar(4000)
set @body1 = '<head>
<title> Embedded Logo Example</title>
<meta name="Generator" content="EditPlus">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
</head>
<body>
<table><tr><td valign="top" align="left">MyHeader</td></tr>
<tr><td valign="top" align="left"><img
src="cid:sqlservercentral_logo.gif" width="235" height="70" border="0"
alt=""></td></tr>
</table>

</body>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name='MyDefault EmailProfile',
@recipients='[email protected]',
@subject = 'SQl 2008 email test',
@body = @body1,
@body_format = 'HTML',
@query = 'SELECT top 3 * from sysobjects where xtype=''U''',
@query_result_header = 0,
@exclude_query_output = 1,
@append_query_error = 1,
@attach_query_result_as_file = 1,
@query_attachment_filename = 'results.txt',
@query_result_no_padding = 1,
@file_attachments = 'C:\sqlservercentral_logo.gif'