RSS

Send mail from SQL database (Configure Database Mail)

12 May

SQL Server Mail

Database mail is newly introduced concept in SQL Server 2005 and it is replacement of SQLMail of SQL Server earlier version. Database Mail has many enhancement over SQLMail. Database Mail is based on SMTP (Simple Mail Transfer Protocol) and also very fast and reliable where as SQLMail is based on MAPI (Messaging Application Programming Interface). Database mail depends on Service Broker so this service must be enabled for Database Mail. Database Mail can be encrypted for additional security. SQLMail is lesser secure as it can encrypt the message as well anybody can use SMTP to send email. Additionally, for MAPI to be enabled for SQLMail it will require Outlook to be installed. All this leads to potential security threat to database server.

In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out:

  1. Create Profile and Account
  2. Configure Email
  3. Send Email

  • Create Profile and Account

SQL.01

SQL.02

SQL.03

SQL.04

SQL.05

SQL.06

SQL.07

SQL.08

SQL.09

SQL.10

  • Configure Email

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE ‘show advanced’, 1
GO

RECONFIGURE
GO

sp_CONFIGURE ‘Database Mail XPs’, 1
GO

RECONFIGURE
GO

SQL.11

  • Send Mail

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GO

EXEC sp_send_dbmail
@profile_name=‘MailProfile’,
@recipients=‘AhmedNegm@WindowsLive.com’,
@copy_recipients = ‘a.negm@outlook.com; a.negm@hotmail.com’, /* CC is Optional */
@blind_copy_recipients = ‘anotherPerson@here.com’, /* BCC is Optional */
@subject= ‘Test Mail Message’,
@body= ‘This is the body of the test message!’,
@query = ‘SELECT * FROM Employee WHERE Absence = 1’; /* Attached query result is Optional */

SQL.12

 

 
Leave a comment

Posted by on May 12, 2014 in MS SQL Server

 

Tags: , , , , , , , , , , , , ,

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: