Microsoft SQL Server supports the system stored procedures
that are used to perform e-mail operations from within an instance of SQL
Server.
This article will explain the process of sending email
through SQL Server Step by step in very easy manners using GMAIL SMTP settings. For
implementing the email setup you just need to follow these steps.
Step 1: Turn on the
component Database Mail XPs
Turn on the component Database
Mail XPs because if this will be disabled you cannot send the email from
SQL Server. By default, this component is blocked for security reasons.
Just copy and paste these
queries in the box into your SQL Server Query window and run it.
SP_CONFIGURE'show advanced
options', 1-- Shows advance options.
RECONFIGUREWITHOVERRIDE
GO
SP_CONFIGURE'Database
Mail XPs', 1-- Enable database mail server.
RECONFIGUREWITHOVERRIDE
GO
SP_CONFIGURE'show
advanced options', 0–- Disabled again.
RECONFIGUREWITHOVERRIDE
GO
On
successfully running these quires this message will appear.
NOTE:If
you missed this step you get this error on sending email.
|
Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
[Batch Start Line 50]
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of
component 'Database Mail XPs' because this component is turned off as part of
the security configuration for this server. A system administrator can enable
the use of 'Database Mail XPs' by using sp_configure. For more information
about enabling 'Database Mail XPs', search for 'Database Mail XPs' in SQL
Server Books Online.
|
Step 2: Create an Account
For Sending Mails
Creates a new Database Mail account holding information about
an SMTP account. You to provide your valid Gmail Account information here such
as Email id, password, display name, reply to etc.
The account you are going to provide here will be used to
send emails. This will be a sender account.
To
create account copy this code snippet and edit with your
information and execute it.
EXEC msdb.dbo.sysmail_add_account_sp
@account_name
='Mahmood_Mail_Account'
,@description
='Send emails using SQL Server Stored Procedure'
,@email_address
='youremail573@gmail.com'
,@display_name
='Mahmood Raza Khan'
,@replyto_address
='youremail@gmail.com'
,@mailserver_name
='smtp.gmail.com'
,@username
='youremail@gmail.com'
,@password
='Password'
,@port = 587
,@enable_ssl
= 1
GO
Step 3: CreateProfile for
sending email in SQL Server
A Database Mail profile holds any number of Database Mail
accounts. Database Mail stored procedures can refer to a profile by either the
profile name or the profile id generated by this procedure.
The profile name and description can be changed with the
stored procedure sysmail_update_profile_sp, while the profile id remains constant
for the life of the profile.
To create profile copy this code snippet and edit with your
information and execute it.
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name
='Mahmood_Email_Profile'
,@description
='Send emails using SQL Server Stored Procedure'
GO
Step 4: Add Account to
Profile
Stored
Proceduresysmail_add_profileaccount_sp is used to add an account to profile.
The
stored procedure sysmail_add_profileaccount_sp is in the msdb database and is
owned by the dbo schema. The procedure must be executed with a three-part name
if the current database is not msdb.
To add an Account to a
Profile copy this code snippet and edit
with your information and execute it.
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name
='Mahmood_Email_Profile'
,@account_name ='Mahmood_Mail_Account'
,@sequence_number
= 1
GO
Step 5: Send Email By
sp_send_dbmail Stored Procedure
Congratulations!!! Until this step you have setup your email
configurations successfully.
But still, you may not receive email click here you will the
find what to do. You need to enable access to less secure apps.
Now sends an e-mail message to the specified recipients. The
message may include a query result set, file attachments, or both. When mail is
successfully placed in the Database Mail queue, sp_send_dbmail returns the
mailitem_id of the message. This stored procedure is in the msdb database.
For a complete reference of sp_send_dbmail Click here
To send email copy this code snippet and edit with your
information and execute.
EXEC msdb.dbo.sp_send_dbmail
@profile_name
='Mahmood_Email_Profile'
,@recipients
='recipient@gmail.com'
,@subject
='Email from SQL Server'
,@body ='This is
my First Email sent from SQL Server :)'
,@importance
='HIGH'
GO
I received an email from sql server.
Step 6: Check Your Mail
Status
You can check your email status you have sent using
sp_send_dbmail in sql server. Execute these three Store Procedure
If your email has unsent yet due to any reason
You will find a row in this sp
1) sysmail_unsentitems
If your email was sent successfully
You will find a row in this sp
2) sysmail_sentitems
If your email has not sent due to any reason
You will find a row in this sp
3) sysmail_failditems
Simply copy and execute
these queries to check your email status
SELECT*FROM msdb.dbo.sysmail_unsentitems
SELECT*FROM msdb.dbo.sysmail_sentitems
SELECT*FROM msdb.dbo.sysmail_faileditems
Summary
In the article I have explained how you can send email
using sql server and how you can check your email status. Hopefully you have
understand the whole process successfully if you have any inconvenience
regarding this process leave a comment under this article I will reply on you
comment with answer.
