1.
Background
The purpose of this article is to describe implementation
of database mail configuration in SQL Server so that Database Mail can be
utilized for sending email in various applications. Database Mail is easy to
configure and maintain as well as one of the simpler way of implementing email
notifications in the application.
of database mail configuration in SQL Server so that Database Mail can be
utilized for sending email in various applications. Database Mail is easy to
configure and maintain as well as one of the simpler way of implementing email
notifications in the application.
2.
Pre-requisite
1. Database Mail configuration should be setup
by service account which will be used for sending email notification.
by service account which will be used for sending email notification.
2. SQL Server Agent service should run.
3. Service Account should have right of
sending mail.
sending mail.
3. Step by Step procedure to implement database mail:
i. Go to SQL Server Management Studioè Connect to the
required SQL serve instanceè go to
Management folderèDatabase
MailèRight click on the
Database Mailè click on Configure
Database Mail:
ii.
It will open the database
mail configuration wizard, Click next
iii. Select the highlighted Database mail
option and click next
iv. Give the name of the Profile
e.g SQLCircuit and Description.
Click on Add
v. Specify the details for
the account which will be used for sending the mail e.g IndiaSQLCircuit :
We are using the following details for configuring
Database Mail:
Database Mail:
|
Account Name
|
IndiaSQLCircuit
|
|
SMTP Server
|
Smtp.gmail.com
|
|
SMTP port
|
465
|
|
Authentication
|
·
We are using Basic authentication here. For basic authentication, we need to pass store username and password in the database profile itself to authenticate on the SMTP server.
·
Windows Authentication is useful when we use SQL Server service account for authentication in SMTP server.
·
Anonymous Authentication: The SMTP server does not require any authentication. Database Mail will not use any credentials to authenticate on the SMTP server |
vi. Now click on next to map
Account to the profile. Here we have added Account name “IndiaSQLCircuit” to profile “SQLCircuit”
vii. Click next. It will open “Manage Profile
Security”. Here you can make the profile public or private based on the
requirement. Also you can set the profile as default.
viii. Click next, it will show
system parameters for Database Mail
ix. Click Next to complete the
wizard
Click finish.
4.
How to send email using Database Mail
i. For unit testing, go to
Database mail, right click and select Send
Test E-Mail
ii. Select the SQLCircuit Profile and give the email address to which you want to
send notification. For testing purpose, I am sending mail to sqlcircuit@gmail.com Click on ‘Send Test E-Mail’.
iii. Check the email and confirm that whether
email is received or not. Below screenshot is showing that we have received the
mail.
iv.
We can send mail by using sp_send_dbmail system procedure. sp_send_dbmail is used send mail by
using database mail profile configured in SQL Server. Use the below code to
send mail to sqlcircuit@gmail.com
DECLARE @SUBJECTMESSAGE
NVARCHAR(500)
NVARCHAR(500)
DECLARE @tableHTML
NVARCHAR(500)
NVARCHAR(500)
SET @SUBJECTMESSAGE= ‘Database Mail
configuration has been completed’
configuration has been completed’
SET @tableHTML
= ‘This is to inform
you that Database Mail configuration has been setup in SQLCircuit Server.
= ‘This is to inform
you that Database Mail configuration has been setup in SQLCircuit Server.
Please utilize the notification services and
please contact us at sqlcircuit@gmail.com for any queries
please contact us at sqlcircuit@gmail.com for any queries
Thanks,
SQLCircuit Team
‘
EXEC msdb.dbo.sp_send_dbmail
@recipients=‘sqlcircuit@gmail.com’,
@subject = @SUBJECTMESSAGE,
@Profile_Name=‘sqlcircuit’, –Profile of SMTP
Server
Server
@body = @tableHTML,
@body_format = ‘HTML’ ;
v. Check the email and confirm that whether
email is received or not. Below screenshot is showing that we have received the
mail.
5.
How to see the Database Mail
Log
For checking the log of Database
mail, right click on Database Mail and Select “View Database Mail Log”
mail, right click on Database Mail and Select “View Database Mail Log”
It will show the complete log of
Database Mail. We can utilize the log for troubleshooting the issues with
database mail.
Database Mail. We can utilize the log for troubleshooting the issues with
database mail.
6. Conclusion
By using
the above steps, we can configure the Database Mail and can be utilize for sending
email notification in various application.
the above steps, we can configure the Database Mail and can be utilize for sending
email notification in various application.
—————————————————-End
of Document—————————————————
of Document—————————————————