Home > DBA Stuff, SQL Server 2005 > SQL DBA – Database Mail Setup – SQL Server 2005

SQL DBA – Database Mail Setup – SQL Server 2005

September 29, 2010 Leave a comment Go to comments

Database Mail, a new addition to the SQL Server 2005 database engine, is an enterprise solution for sending e-mail messages from the Microsoft SQL Server 2005 Database Engine. 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 designed for reliability, scalability, security, and supportability.

Database Mail provides a robust, high-performance replacement for the most commonly requested features of SQL Mail. Database Mail is designed to operate with SMTP servers, and is tested with Microsoft SMTP servers.

Note: Database Mail is not active by default. To use Database Mail, you must explicitly enable Database Mail by using either the “SQL Server Surface Area Configuration” tool or the “Database Mail Configuration Wizard“.

Database Mail Setup:

Enabling the Database Mail feature: By default the Database mail Stored-Procs are disabled.

For Express Edition, check the required files exists in MSSQL\Binn folder. If not then copy the following 3 files:
1. DatabaseMail90.exe
2. DatabaseMailEngine.dll
3. DatabaseMailProtocols.dll

Then execute the following Stored-Porcs:

exec dbo.sysmail_start_sp
exec dbo.sysmail_stop_sp


A User must be a member of the DatabaseMailUserRole database role in the msdb database.

To Enable Database Mail execute the following block of code:

use master
exec sp_configure 'show advanced options', 1
exec sp_configure 'Database Mail XPs', 1


Initial Cleanup:

SELECT * FROM msdb.dbo.sysmail_profileaccount pa
      JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id
      p.name = @ProfileName AND
      a.name = @AccountName)
      PRINT 'Deleting Profile Account'
      EXECUTE sysmail_delete_profileaccount_sp
      @profile_name = @ProfileName,
      @account_name = @AccountName

SELECT * FROM msdb.dbo.sysmail_profile p
WHERE p.name = @ProfileName)
      PRINT 'Deleting Profile.'
      EXECUTE sysmail_delete_profile_sp
      @profile_name = @ProfileName

SELECT * FROM msdb.dbo.sysmail_account a
WHERE a.name = @AccountName)
      PRINT 'Deleting Account.'
      EXECUTE sysmail_delete_account_sp
      @account_name = @AccountName


Setting up Accounts & Profiles for DB Mail:
Create a Database Mail account for holding information about an SMTP account.
An Account holds information for only one e-mail server, such as the account name, e-mail address, reply-to e-mail address, server name or IP address, and some optional security settings.

--// Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Test Mail Account',
    @description = 'Mail account for administrative e-mail.',
    @email_address = 'abc@xyz.com',
    @replyto_address = 'abc@xyz.com',
    @display_name = 'Manoj Pandey',
    @mailserver_name = 'smtp.xxxx.net',
	@port = 587,
	@username = 'xyz',
	@password = 'xxyyzz',
	@enable_ssl = 1

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Test Mail Profile',
    @description = 'Profile used for administrative mail.'

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Test Mail Profile',
    @account_name = 'Test Mail Account',
    @sequence_number =1

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Test Mail Profile',
    @principal_name = 'public',
    @is_default = 1


Sending Mail and Check its status:

--Send mail
EXEC msdb.dbo.sp_send_dbmail
	@body= 'Test Email Body',
	@subject = 'Test Email Subject',
	@profile_name = 'Test Mail Profile'
--Send mail with attachment
EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'DBMail'
    ,@recipients = 'GroupSQLDBA@MyCo.com'
    ,@from_address = 'DBMail@MyCo.com'
    ,@query = 'SELECT resource_type, resource_database_id,
                    request_mode, request_session_id
               FROM sys.dm_tran_locks
              WHERE request_mode IN (''IX'', ''X'')'
    ,@subject = 'Exclusive Locks'
    ,@attach_query_result_as_file = 1 ;


Important Tables used in configuring Database mail and check their status:

SELECT * FROM msdb.dbo.sysmail_profile

SELECT * FROM msdb.dbo.sysmail_account

--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount

--Principal Profile
select * from msdb.dbo.sysmail_principalprofile

--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration

--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems

--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',
FROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail
ON fail.mailitem_id = LOG.mailitem_id
WHERE event_type = 'error'

--Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp

--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp


Note: After performing above steps the mails were not getting sent. I was configuring DB mail from my client machine, even with the admin account. I was getting various errors like:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 19 (2010-10-22T07:24:21). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond IP_ADD:25).

Then I tried to setup the DB mail from the server itself and it worked in one go.

  1. September 29, 2010 at 1:57 pm

    Nice article

  2. October 25, 2010 at 2:05 pm

    Database Mail Fails For SQL Server Agent Jobs / Maintenace plans – Why ? Link: http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/32a2c09a-156f-43b2-9076-b11ba9ccdafb

  3. October 25, 2010 at 2:05 pm

    Database Mail – test is OK, but jobs do not notify. Link: http://www.sqlservercentral.com/Forums/Topic263951-92-1.aspx

  4. December 8, 2010 at 12:15 pm

    hi Manu,
    very helpful post and thanks for summarizing this in detail.
    Tharindu Dhaneenja

  5. aniruddha
    September 15, 2011 at 10:45 am

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2011-09-15T15:52:30). Exception Message: Could not connect to mail server. (The requested address is not valid in its context
    )…………….. why?

  6. PeterA
    September 9, 2012 at 10:01 pm

    I am having error,
    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2012-09-09T09:27:59). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: Downstream server error). )

    I followed the exact steps above within my SQL 2008 Express and Smtp service is running in windows 2008 server

  7. January 16, 2013 at 9:00 pm


  8. Prateek
    April 11, 2014 at 9:30 pm

    Thank you so much!

    You are the BEST! 🙂

    • April 27, 2014 at 12:43 pm

      thanks @Prateek & @Steve

  9. Zwe Pyae Phyo Oo
    August 17, 2016 at 2:24 pm

    “The mail could not be sent to the recipients because of the mail server failure. ”
    That’s why server name is wrong.

    If you don’t know your server name, open ‘cmd’.
    It will show Default Server name and then we can type it in Database Mail Configuration window.
    Open Sql>>Management>>Database Mail [Right Click]>>Configure Database Mail>>Next>>Next>>Add

    Note. Database Mail for Gmail
    server name : smtp.gmail.com
    Port Number : 587
    check the checkbox : This server requires a secure connection (SSL)
    select Basic Authentication and fill username and password for your sender account

  1. January 2, 2011 at 10:31 pm
  2. July 29, 2011 at 9:53 am
  3. July 7, 2015 at 2:15 pm

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: