Configuring Database Mail In SQL Server

Steps to configure database mail in SQL Server:

Step 1: Open SQL Server.

Step 2: Select Object Explorer.

Step 3: Management.

Step 4: Right click on "Database Mail" and select “Configure Database Mail” as follows:



Step 5: Hit "Next" button.

Step 6: Choose the option “Setup Database Mail by performing the following tasks” as below:



Step 7: Hit on "Next" button.

Step 8: Enter profile name as “TestProfile” and description as follows:



Step 9: Hit  “Add” button and setup your own "New Database Mail Account" details. If you have your own Mail configuration details(like Server name, email address etc.) then you can use them to configure your Mail account. If you want to configure Mail account in your company database then you need Mail configuration details those you can get it from Admin team or any other responsible team that varies from company to company. The required Mail configuration details as below:

• Unique email id for SMTP Email Address
• SMTP Server name
• SMTP Port number
• SSL feature for secure connection should be disabled or enabled.

Step 10:



Step 11: Click on "OK"

Step 12: Click on "Next"

Step 13: In "Manage Profile Security" section, select the check box for the created profile and set "Default Profile" to "Yes". For reference find below screenshot:



Step 14: Click on "Next" button.

Step 15: After hitting  "Next" button you will get the screen as below as below with default settings for "System. Parameters":



Step 16: Hit on "Next" button.

Step 17: Hit on "Finish" button.



Step 18: Hit on "Close" button.

We have successfully completed database mail configuration. Now it's time to test the configured setting. To test the configured database mail setting:

We can test the configured mail setting in two ways:

1. Using "Send Test Email": Go to Object Explorer => Management => Right click on Database Mail => Send Test Email.
   
   
   
   Enter the below required details:
   
   
   • Database Mail Profile: We created newly database profile as "TestProfile"
   • To: To whom you want to send a mail
   • Subject: You can write your own subject
   • Body: You can write your own body that is based on your need.
   
   
   
   Click on "Send Test Email".
   
   
2. Test configured Mail setting using "SP_SEND_DBMAIL"
   
   We can send mail programmatically by calling system procedure "sp_send_dbmail" within any configured SQL job or stored procedure. Before sending the mail we have to check for the required parameters by viewing the definition of stored procedure.
   
   
   
   We have to pass the minimum parameters as below to the above stored procedure. I have placed the dummy inputs for the following parameters:
   
   
   • Profile_name (We can mention the profile name which we have created in "Step - 10")
     
     
   • Recipients (We can mention multiple recipients by separating with ‘;’)
     
     
   • Subject (You can give our own subject as per our need)
     
     
   • Body (We can set our own body part based on the business need)

    

   EXEC msdb.dbo.sp_send_dbmail
   @profile_name ='TestProfile',
   @recipients ='testmail.com',
   @subject ='Test mail',
   @body='Hi, test mail';

   Verify whether the mail was send successfully or not. Take new query and run the following command and check the "sent_status" column value for your sent mail as follows:
   

   use msdb
   go
   select * from sysmail_allitems

    

Note: In this article I have useda few commands and syntaxes that may match those from online.