SQL SERVER 2008 Configure Database Mail – Send Email From SQL Database Using sp_send_dbmail

SQL SERVER 2008 Configure Database Mail – Send Email From SQL Database

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

Create Profile and Account

Configure Email

Send Email

I am just providing the steps that are enough to give you the basic undrstanding to send a mail from the database to your mail hosting server.

Here for example i am using the Gmail SMTP server details to send a test e-mail.

-- 1) First Enable the Database Mail XPs:

sp_helptext sysmail_add_account_sp


USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO


-- 2) Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA Mail',
@description = 'Mail account for administrative e-mail.',
@email_address = 'ycuisthebest@gmail.com',
@replyto_address = 'ycuisthebest@gmail.com',
@display_name = 'DBA Mail',
@mailserver_name = 'smtp.gmail.com',
@port = 25,
@username = 'ycuisthebest@gmail.com',
@password = 'xxxxxx',
@enable_ssl = 1

-- 3) Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Mail',
@description = 'Profile used for administrative mail1.'

-- 4) Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA Mail',
@account_name = 'DBA Mail',
@sequence_number =1

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


-- Send A Test E-Mail With Plain Text
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBA Mail',
@recipients='ycuisthebest@gmail.com',
@subject='My First Test message',
@body='Message Sent Successfully'


-- Send A Test E-Mail with query result here i am just sending the current date as a result set
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBA Mail1',
@recipients='yogeshsangrajani@gmail.com',
@subject = 'Current date time Values',
@query = N'NOCOUNT ON SELECT getdate();',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Memory Values.txt',
@body='Message with date Sent Successfully'

--  Send A Test E-Mail with query result in HTML Format 

DECLARE @Body NVARCHAR(MAX),

    @TableHead VARCHAR(1000),

    @TableTail VARCHAR(1000)  

SET @TableTail = '

' ;

SET @TableHead = '' + '' + '' + '' + 'Report generated on : '

    + CONVERT(VARCHAR(50), GETDATE(), 106) 

    + '
'

    + '
PO Date
'

    + '
ID
'

    + '
Name
'

    + '
Department
'

    + '
Age
'

    + '
Salary
'

    + '
Location

' ;

SET @Body = ( SELECT    td = E.ID, '',

                        td = E.Name, '',

                        td = E.Dept, '',

                        td = E.Age,'',

                        td = E.Salary,'',

                        td = E.Location, ''                      

              FROM      employee E                                      

            FOR   XML RAW('tr'),

                  ELEMENTS

            )  

SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail

  

EXEC msdb.dbo.sp_send_dbmail

@profile_name='DBA Mail1',

@recipients='yogeshupr@gmail.com',  

@subject='Query Result',

@body=@Body,

@body_format = 'HTML' ;   

Some other aspect that will definitely required in the testing phase
You can update the smtp server setting within the following TABLE msdb.dbo.sysmail_server

SELECT * FROM msdb.dbo.sysmail_server
UPDATE msdb.dbo.sysmail_server SET enable_ssl =1, port=25 --etc.,


Usefull table that are required to monitoring the send mail details

Quick troubleshooting queries for Database Mail

SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_account
SELECT * FROM msdb.dbo.sysmail_faileditems
SELECT * FROM msdb.dbo.sysmail_configuration
SELECT * FROM msdb.dbo.sysmail_mailitems


-- Check to see if the service broker is enabled (should be 1):


SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'


-- Check to see if Database Mail is started in the msdb database:


EXECUTE msdb.dbo.sysmail_help_status_sp


-- Start Database Mail if necessary:


EXECUTE msdb.dbo.sysmail_start_sp


-- Check the status of the mail queue:


EXECUTE msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail'


-- Check the Database Mail event logs:


SELECT * FROM msdb.dbo.sysmail_event_log


-- Check the mail queue for the status of all items (including sent mails):


SELECT * FROM msdb.dbo.sysmail_allitems


It Can Happens: Following Error Solution Link Given Below:


The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2015-12-10T10:24:09). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at). )


-- Usefull References: http://www.technologycrowds.com/2015/05/smtp-server-authentication-server-response-5-5-1-Authentication-Required-in-Gmail.html


-- Code Project Using Wizard: http://www.codeproject.com/Articles/29060/SQL-SERVER-Configure-Database-Mail-Send-Email