In this article I will explain with an example, how to send email in
SQL Server through Gmail SMTP.
This article is applicable to following
SQL Server versions i.e. 2008, 2008R2, 2012, 2014, 2016, 2017, 2019 and 2022.
Using MASTER Database
For all queries related to email, you need to use the MASTER Database as all the tables exist in the MASTER database.
Unblocking the sp_send_dbmail Stored Procedure in SQL Server
If the
Stored Procedure is executed while it is blocked, you will get the following error.
![Send SQL Server database mail through Gmail SMTP]()
To unblock the
Stored Procedure, simply copy, paste and execute the following SQL queries.
USE MASTER
GO
SP_CONFIGURE 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'Database Mail XPs', 1
RECONFIGURE WITH OVERRIDE
GO
SP_CONFIGURE 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO
Once executed, the following messages will appear which confirms, everything went OK and
sp_send_dbmail Stored Procedure is now unblocked.
![Send SQL Server database mail through Gmail SMTP]()
Creating Account for sending emails in SQL Server
This
Stored Procedure accepts necessary parameter which contains Mail Server settings as UserName, Password, Port and EnableSsl etc.
Note: It is necessary to use the sender’s email address credentials while defining the Gmail SMTP Server Credentials as the sender’s email address must be same as the Gmail Username specified in credentials.
To create an Account, simply copy, paste, edit (according to your settings) and execute the following SQL query.
Note: Make sure you enter valid Gmail SMTP Server Credentials.
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'Mudassar_Mail_Account'
,@description = 'Send emails using SQL Server Stored Procedure'
,@email_address = 'youremail@gmail.com'
,@display_name = 'Mudassar Ahmed Khan'
,@replyto_address = 'youremail@gmail.com'
,@mailserver_name = 'smtp.gmail.com'
,@username = 'youremail@gmail.com'
,@password = 'GMAIL PASSWORD'
,@port = 587
,@enable_ssl = 1
GO
Note: For complete documentation on
sysmail_add_account_sp Stored Procedure, please refer
here.
Creating Profile for sending email in SQL Server
The Profile contains information about Account for which the Account needs to be added to a Profile as it is necessary to provide the name of the Profile while sending email using
sp_send_dbmail Stored Procedure in
SQL Server.
To create a profile, simply copy, paste, edit (according to your settings) and execute the following SQL query.
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Mudassar_Email_Profile'
,@description = 'Send emails using SQL Server Stored Procedure'
GO
Note: For complete documentation on
sysmail_add_profile_sp Stored Procedure, please refer
here.
Adding Account to Profile for sending email in SQL Server
The sysmail_add_profileaccount_sp Stored Procedure is used to add Account to Profile.
To add account to profile, simply copy, paste, edit (according to your settings) and execute the following SQL query.
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Mudassar_Email_Profile'
,@account_name = 'Mudassar_Mail_Account'
,@sequence_number = 1
GO
Note: For complete documentation on
sysmail_add_profileaccount_sp Stored Procedure, please refer
here.
Sending Email using sp_send_dbmail Stored Procedure in SQL Server
Note: All the above steps i.e. unblocking sp_send_dbmail, creating Account, creating Profile and adding Account to Profile are to be done only ONCE as all these settings are being saved in the SQL Server Database.
To send an email, simply copy, paste, edit (according to your settings) and execute the following SQL query.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mudassar_Email_Profile'
,@recipients = 'recipient@gmail.com'
,@subject = 'Email from SQL Server'
,@body = 'This is my First Email sent from SQL Server :)'
,@importance ='HIGH'
GO
Once executed, the following messages will appear which confirms, your email has been queued.
![Send SQL Server database mail through Gmail SMTP]()
Checking Emails Status and Errors using Log Tables
The following tables can be used to find the status of Emails whether they are not sent, sent or failed.
Unsent Emails
The Emails which are not sent due to any reason are available in the msdb.dbo.sysmail_unsentitems table.
SELECT * FROM msdb.dbo.sysmail_unsentitems
Sent Emails
The Emails which were sent without any problems are available in the msdb.dbo.sysmail_sentitems table.
SELECT * FROM msdb.dbo.sysmail_sentitems
Failed Emails
The Emails which are failed and were not sent are available in the msdb.dbo.sysmail_faileditems table.
SELECT * FROM msdb.dbo.sysmail_faileditems
Above table does not store the details of the Error i.e. the Error Message. The details of the Error are present in the msdb.dbo.sysmail_event_log table.
The following SQL Query gets list of failed emails as well as the details of the Error.
SELECT mailitem_id
,[subject]
,[last_mod_date]
,(SELECT TOP 1 [description]
FROM msdb.dbo.sysmail_event_log
WHERE mailitem_id = logs.mailitem_id
ORDER BY log_date DESC) [description]
FROM msdb.dbo.sysmail_faileditems logs
Possible Errors
The possible errors (exceptions) occurring while sending email using GMAIL SMTP are covered in the following article.
Screenshot
![Send SQL Server database mail through Gmail SMTP]()
Downloads
