How to configure and Send Database mail in SQL Server

Share us on:

Hi, I am Shubham Kumar, I will explain and implement how to configure and send mail from SQL Server by SQL Script(Query).

I will explain in the easiest way, How to configure and send mail from SQL Server(MSSQL).

I will use SQL Script(Query) to configure and send mail from SQL Server(MSSQL).

MSSQL gives users to send mail from the database. you can use this functionality in different - different situations like, If you want to send mail at a specific time then you can use SQL Agent, which will call procedure, where configured settings for send mail. 

In a previous article, I have discussed, How to Configure SQL Server Mail using Configuration Wizard.

Now, I am going to discuss how to Make settings for sending mail from the database. 

Firstly you need to know the database is mail-enabled or not, if you want to know database mail is enabled or not you can check in this table "sys.configurations", If columns value_in_use is "0", it means it is not enabled if "1" then it is enabled. 

 SELECT * FROM sys.configurations WHERE namr="Database Mail XPs"

Step #1: Enable Database Mail

You can enable Database mail using SQL Query(T-SQL). This is the first step for configuring SQL Server Mail.

You can enable Database mail settings by Procedure(sp_configure). There is a T-SQL Query, it's used to enable Database mail settings.

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Step #2: Create Database Mail Account

In SQL Server, There is a procedure "sysmail_add_account_sp", it's used to we can create a Database Mail Account.

There are some parameters in procedure(sysmail_add_account_sp).

@account_name: Provide name for Database Mail Account. 

@description: Give a description for Database Mail Account.

@email_address: Sender Email Address.

@replyto_address: ReplyEmail Address.

@display_name: Sender Display Name.

@mailserver_name: SMTP(smtp.gmail.com, smtp-mail.outlook.com, smtp.mail.yahoo.com, etc.) Name.

 
-- Create a Database Mail Account

 EXECUTE msdb.dbo.sysmail_add_account_sp    
 @account_name = 'DEMO SQL Mail',    
 @description = '',    
 @email_address = 'abc@gmail.com',    
 @replyto_address = '',    
 @display_name = 'DEMO Customer Care',    
 @mailserver_name = 'smtp.gmail.com' ;    

Step #3: Create Database Mail Profile. 

In SQL Server, There is a procedure "sysmail_add_profile_sp", it's used to we can create a Database Mail Profile.

There are some parameters in the procedure(sysmail_add_profile_sp) for creating a Database Mail Profile.

@profile_name: Set profile name for Database Mail Profile. 

@description: Set a description for Database Mail Profile.

-- Create a Database Mail profile    

 EXECUTE msdb.dbo.sysmail_add_profile_sp    
 @profile_name = 'DEMO SQL Mail',    
 @description = '' ;  

Step #4: Add the Database Mail Account to the profile.

In SQL Server, There is a built-in procedure "sysmail_add_profileaccount_sp", Which contains some parameters @profile_name, @account_name, @sequence_number, It used to we can Add(Link) Database Mail Account to the profile.

@profile_name: Set default profile name for Database Mail. 

@account_name: Set Account Name for Database Mail.  

@sequence_number: Set Default sequence_number for Database Mail. 

-- Add the account to the profile  

 EXECUTE msdb.dbo.sysmail_add_profileaccount_sp    
 @profile_name = 'DEMO SQL Mail',    
 @account_name = 'DEMO SQL Mail',    
 @sequence_number =1 ;

Step #5: Grant access to the profile to the DBMailUsers role.

In SQL Server, There is system defined procedure(sysmail_add_principalprofile_sp), It's used to we can Grant Access to the profile.

There are some parameters in the procedure(sysmail_add_principalprofile_sp).

@profile_name: set profile name for DB mail, It is a mandatory field.

@principal_name: It is a not mandatory field.

@is_default: It is a mandatory field.

-- Grant access to the profile to the DBMailUsers role 

 EXECUTE msdb.dbo.sysmail_add_principalprofile_sp    
 @profile_name = 'DTS SQL Mail',    
 @principal_name = '',    
 @is_default = 1 ; 

Step #6: Send Mail Using T-SQL(Query).

In SQL Server, There is system defined procedure(Sp_send_dbmail), It's used to we can Send Mail from the Database.

   EXEC msdb.dbo.Sp_send_dbmail   
    @profile_name = 'DEMO SQL Mail',     
    @body = 'Hi, i am shubham kumar. this is test mail.',   
    @body_format ='HTML',      
    @recipients = 'abc@gmail.com',   
    @copy_recipients ='abc1@gmail.com',  
    @blind_copy_recipients ='abc3@gmail.com',  
    @subject = 'Test Mail'; 

In SQL Server, There are some important tables for knowing information about Mail.

    -- Contains necessary information to interact with SMTP server
	 select * from sysmail_account
     
    -- It Contains useful logs if something goes wrong.
	 select * from sysmail_log
     
    -- Contains email sent details.
	 select * from sysmail_mailitems
     
    -- That contains flags, bits, and values that drive email behavior
	 select * from sysmail_configuration
     
    -- Profile account details for email
	 select * from sysmail_profile
     
    -- sysmail_profileaccount table between sysmail_profile and sysmail_account
	 select * from sysmail_profileaccount
    
    -- It Contains configuration settings the SMTP server used to send mail
	 select * from sysmail_server

I hope this article will help you to learn, how to configure and send mail from SQL Server by SQL Script(T-SQL). if you like this article please share Us on