Click here to Skip to main content
15,867,835 members
Articles / Database Development / SQL Server

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database

Rate me:
Please Sign up or sign in to vote.
4.88/5 (32 votes)
3 Sep 2008CPOL2 min read 221K   73   14
SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database

Introduction

In this article, I discuss about the Database Mail which is used to send the email using SQL Server. Previously I had discussed about SQL SERVER - Difference Between Database Mail and SQLMail. 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 2: Configure Email

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

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

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:

SQL
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.' 

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 the sysmail_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:

SQL
SELECT * FROM sysmail_mailitems GO SELECT * FROM sysmail_log GO 

Status can be verified using sysmail_sentitems table.

After sending mail, you can check the mail received in your inbox, just as I received as shown below:

Let me know what you think about this tutorial.

Reference: Pinal Dave (http://www.SQLAuthority.com)

History

  • 3rd September, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Founder http://blog.SQLAuthority.com
India India
Pinal Dave is a Microsoft Technology Evangelist (Database and BI). He has written over 2200 articles on the subject on his blog at http://blog.sqlauthority.com. Along with 8+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). He is co-author of two SQL Server books - SQL Server Programming, SQL Wait Stats and SQL Server Interview Questions and Answers. Prior to joining Microsoft he was awarded Microsoft MVP award for three continuous years for his contribution in community.

Comments and Discussions

 
QuestionDatabaseMail error Pin
Member 110588164-Sep-14 3:00
Member 110588164-Sep-14 3:00 
QuestionNewletter Subscription Pin
Member 102820745-Oct-13 1:48
Member 102820745-Oct-13 1:48 
QuestionMails Sending from SQL receiving in SPAM/Junk Pin
Member 100241352-May-13 1:16
Member 100241352-May-13 1:16 
GeneralMy vote of 5 Pin
Member 37463078-Apr-13 23:46
Member 37463078-Apr-13 23:46 
GeneralMy vote of 5 Pin
Lokesh Zende19-Dec-12 2:10
professionalLokesh Zende19-Dec-12 2:10 
GeneralMy vote of 5 Pin
Kishor_Mahale28-Nov-12 18:42
Kishor_Mahale28-Nov-12 18:42 
GeneralMy vote of 5 Pin
Amol_B4-Jun-12 19:57
professionalAmol_B4-Jun-12 19:57 
GeneralMy vote of 5 Pin
P.Salini25-May-12 1:44
P.Salini25-May-12 1:44 
Nice Useful to me
QuestionDatabase Mail Sending Failed From Sql Server 2008 R2 . Error Like Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions Pin
GV Sharma3-May-12 1:10
GV Sharma3-May-12 1:10 
QuestionDatabase Mail Sending Failed From Sql Server 2008 R2 . Error Like Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions ? Pin
GV Sharma3-May-12 1:09
GV Sharma3-May-12 1:09 
AnswerRe: Database Mail Sending Failed From Sql Server 2008 R2 . Error Like Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions ? Pin
Kanwal Shehzad31-Jul-12 2:26
professionalKanwal Shehzad31-Jul-12 2:26 
GeneralMy vote of 5 Pin
gemasoft11-Mar-11 7:45
gemasoft11-Mar-11 7:45 
GeneralWow 5/5 Pin
prasad0229-Jul-09 4:46
prasad0229-Jul-09 4:46 
QuestionHow i can make associatin with asp.net web page? Pin
Ahmed R El Bohoty14-Oct-08 0:31
Ahmed R El Bohoty14-Oct-08 0:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.