Click here to Skip to main content
14,923,844 members
Articles / Database Development / SQL Server / SQL Server 2012
Tip/Trick
Posted 25 Nov 2014

Tagged as

Stats

118.3K views
25 bookmarked

How To Send Mail Using SQL Server: Part 1

Rate me:
Please Sign up or sign in to vote.
4.66/5 (14 votes)
25 Nov 2014CPOL2 min read
In this article, we are going to learn the concept of mail sending using SQL Server.

Introduction

There are three parts for this tutorial:

Step 1

Log in to SQL Server 2008 with a correct user name and password.

Step 2

Expand the Management tab, then select SQL Server Logs, then right-click on Database Mail, then select Configure Database Mail.

Picture1

Step 3

The following window appears:

Picture2

Click Next.

Step 4

Select the Set up Database Mail by performing the following tasks: radio button.

Picture3

Step 5

Click Next, then a confirmation box appears; click OK on that.

Picture4

Step 6

When you click OK, then write the profile name description and click on Add.

Picture5

Step 7

Then, a new window appears where you provide your profile name and configure Outgoing Mail Server(SMTP) then click Basic authentication and provide your email id and password then click OK.

Picture6

Step 8

Then in the new window, it will show your profile, check that and click Next.

Picture7

Step 9

Then in the new window, the Configure System Parameters description is shown.

Picture8

Step 10

Click Next. It will show a confirmation about your profile; click Finish.

Picture9

Step 11

Now the email service has been configured, click Close.

Picture10

Step 12

Send a test email by right-clicking on database mail and click Send Test E-Mail.

Picture11

Step 13

Provide the Test Email Id and click On Send Test E-Mail. Then check your mail to see if you got the mail. Enjoy!!

Picture12

Note

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 using 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

Output Message

The Configuration option "show advanced options" changed from 0 to 1. Run the RECONFIGURE statement to install.

The configuration option "Database Mail XPs" changed from 1 to 1. Run the RECONFIGURE statement to install.

Use the following query to send mail using query:

Send Mail Query

USE msdb;--Must Use msdb database, otherwise you will get error.

SQL
GO
EXEC sp_send_dbmail @profile_name='MyTestMail',
@recipients='manishki@live.com',
@subject='My Test Mail Service.',
@body='Database Mail Received Successfully.'

This is the body of this message.

Note

If you are using a database other than msdb, then use a stored procedure like msdb.dbo.sp_send_dbmail. The log can be checked in sysmail_log table as shown below:

SQL
SELECT * FROM sysmail_mailitems
GO
SELECT * FROM sysmail_log
GO
select * from sysmail_log

Summary

In this illustration, you learned about configuring and sending mail using SQL Server 2008. Please provide your valuable comments about this article.

License

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

Share

About the Author

Manish kumar choudhary
Software Developer
India India
Manish Kumar Choudhary is a passionate and pragmatic software engineer specializing in web application development with ASP.NET MVC, Web API, Entity Framework, NHibernet, Angular, Backbone, HTML5, and CSS. He started programming at the age of twelve on VB.Net and fell in love with it. His dream at the time was to become a software engineer so he pursues programming both academically and professionally. He has a Bachelor of Science in Computer Application and a Master of Science in Computer Application. He has over 3.5 years’ professional experience developing web applications. Outside the software world, he enjoys photography, swimming, cricket and football. Manish is based in Hyderabad, India.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 135523107-Mar-21 0:29
MemberMember 135523107-Mar-21 0:29 
QuestionIt is very helpfull Nice article Pin
murali koathuru28-Jun-18 22:53
Membermurali koathuru28-Jun-18 22:53 
Questionsql email Pin
Member 128331514-Nov-16 8:23
MemberMember 128331514-Nov-16 8:23 
GeneralMy vote of 5 Pin
Member 1236439028-Mar-16 22:46
MemberMember 1236439028-Mar-16 22:46 
Questiongood Pin
Member 1111981626-Nov-14 14:32
MemberMember 1111981626-Nov-14 14:32 
GeneralMy vote of 5 Pin
JohannQ26-Nov-14 11:49
MemberJohannQ26-Nov-14 11:49 

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.