/* ------------------------ My Meta Content Here SEO ------------------------ */


Main Menu

Thursday, December 10, 2015

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

-- 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',
@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',
@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 
    @TableHead VARCHAR(1000),
    @TableTail VARCHAR(1000)  

SET @TableTail = '
' ;
SET @TableHead = '' + '' + '' + '' + 'Report generated on : '
    + CONVERT(VARCHAR(50), GETDATE(), 106) 
    + '
    + '
PO Date
    + '
    + '
    + '
    + '
    + '
    + '
' ;

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'),
SELECT  @Body = @TableHead + ISNULL(@Body, '') + @TableTail
EXEC msdb.dbo.sp_send_dbmail
@profile_name='DBA Mail1',
@subject='Query Result',
@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

No comments:

Post a Comment

My Blog List