Configure SQL Server for sending emails

 Introduction: Hello Guys, “Welcome back” Today, i am here with another one new great article. In this article I explained how to configure SQL Server for sending emails from Database.

This article is suitable for the mentioned SQL Server versions i.e. 2008, 2008R2, 2012, 2014, 2016, 2017, 2019 and 2022.

Unblocking the sp_send_dbmail Stored Procedure in SQL Server

The very first phase is to unblock the Store Procedure sp_send_dbmail used for sending emails using SQL Server.

If Store Procedure is blocked when it executed, You got below error.

To unblock the Store Procedure, you just need to  copy, paste and execute the below SQL queries.

USE MASTER

GO

SP_CONFIGURE 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

GO

 

-- Unblocking sp_send_dbmail

SP_CONFIGURE 'Database Mail XPs', 1

RECONFIGURE WITH OVERRIDE

GO

 

SP_CONFIGURE 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

GO

 

When Store procedure executed successfully, the following messages will appear which confirms, everything went OK and sp_send_dbmail Store Procedure is now unblocked.

-- Enable show options

EXEC sp_configure 'show advanced options',1

RECONFIGURE

GO

-- Enable xp_cmdshell

EXEC sp_configure 'xp_cmdshell',1

RECONFIGURE

GO

 

Creating Account for sending emails in SQL Server

The sysmail_add_account_sp Store Procedure is used to add Account to SQL Server. This Store Procedure accepts compulsory parameter which needed Mail Server settings as UserName, Password, Port and EnableSsl etc.

Note: It is necessary to use the sender’s email Login Details while defining the Gmail SMTP Server Login Details as the sender’s email address must be same as the Gmail Username given in credentials. For details about sysmail_add_account_sp Store Procedure you can check my last article.

 To create an Account, simply copy, paste, edit (according to your settings) and execute the following SQL query. It is make sure that you need to enter valid Gmail SMTP Server Credentials.

 EXEC msdb.dbo.sysmail_add_account_sp

    @account_name = 'SanjeevKumar_Mail_Account'

   ,@description = 'Send emails using SQL Server Stored Procedure '

   ,@email_address = 'youremail@gmail.com'

   ,@display_name = 'Sanjeev Kumar'

   ,@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 Store Procedure  in SQL Server .

The sysmail_add_profile_sp Store Procedure is used to create Profile to SQL Server. For details about sysmail_add_profile_sp Store Procedure you can check my last article.

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 = 'SanjeevKumar_Email_Profile'

   ,@description = 'Send emails using SQL Server Stored Procedure '

GO

Note: For complete article on sysmail_add_profile_sp Stored Procedure , please follow the Link

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 = 'Sanjeev_Kumar_Email_Profile'

   ,@account_name = ' Sanjeev_Kumar_Mail_Account'

   ,@sequence_number = 1

GO

 Note: For complete documentation on sysmail_add_profileaccount_sp Stored Procedure, please refer here. Sending Email in SQL Server using Stored Procedure

Note: For sending email in SQL Server using Store Procedure, please refer my article Send email in  SQL Server using Store procedure.

Conclusion: In above code, I have been explained that how to configure SQL Server for sending emails from Database. This code is very helpful for every developer. Bye Bye and take care of yourself Developers. We will come back shortly with the new article.

 

Regards

Using Asp.net