In this article, I discuss about the Database Mail which is used to send the email using SQL Server.
Database mail is the replacement for SQLMail with many enhancements. So one should stop using SQLMail and upgrade to the Database Mail.
In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out:
  1. Create Profile and Account
  2. Configure Email
  3. Send Email

Step 1: Create Profile and Account

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node.
This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:
step 1.1

step 1.2
step 1.3
step 1.4
step 1.5
step 1.6
step 1.7
step 1.8
step 1.9
step 1.10
step 1.11

Step 2: Configure Email


sp_CONFIGURE 'show advanced', 1 GO RECONFIGURE GO sp_CONFIGURE _
 'Database Mail XPs', 1 GO RECONFIGURE GO

Step 2

Step 3: Send Email

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb GO EXEC sp_send_dbmail @profile_name='PinalProfile', _
@recipients='test@Example.com', @subject='Test message', _
@body='This is the body of the test message. _
Congrats Database Mail Received By you Successfully.'
Step 3.1
After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker. Read more at SQL SERVER - Introduction to Service Broker.
Database Mail keeps copies of outgoing e-mail messages and displays them in the sysmail_allitems,sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems. The status of the mail sent can be seen in sysmail_mailitems table. When the mail is sent successfully, the sent_status field of thesysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that have failed will have the sent_status field value to 2 and those are unsent will have value 3. The log can be checked in sysmail_log table as shown below:

SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO


Step 3.2
Status can be verified using sysmail_sentitems table.

Step 3.3
After sending mail, you can check the mail received in your inbox.

Important Searches :-

  • How to send email from sql server 2008
  • How to send email from sql serve
  • How to send email from sql 
  • How to Configure Mail in Database 
  • How to Configure Database Mail

credits - codeproject