SQL with Manoj

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
go
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Database Mail XPs', 1
reconfigure

Initial Cleanup:

IF EXISTS(
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
WHERE
      p.name = @ProfileName AND
      a.name = @AccountName)
BEGIN
      PRINT 'Deleting Profile Account'
      EXECUTE sysmail_delete_profileaccount_sp
      @profile_name = @ProfileName,
      @account_name = @AccountName
END

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

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

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
	@recipients=N'manub22@gmail.com',
	@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:

--Profiles
SELECT * FROM msdb.dbo.sysmail_profile

--Accounts
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',
       fail.mailitem_id,
       LOG.description
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.