Today in this article I would 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 of the SQLMail with many enhancements. So one should stop using the SQL Mail and upgrade to the Database Mail. Special thanks to Software Developer Monica, who helped with all the images and extensive testing of subject matter of this article.
Here is the video of the same subject:
[youtube=http://www.youtube.com/watch?v=ZGDBB2uwNp8]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:
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:
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.
Congrates 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 are 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
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 (https://blog.sqlauthority.com)
478 Comments. Leave new
Wow
Very,very interesting, i never had of Db Mail before.
I am currently using VB .NET 2008.
My question is, is this what i need to send emails.
Can i get data from the database.
Do you something i can us when i want to send SMS?
How do send email from Microsoft Access using VB .NET 2008?
Thank you
Imran you promised screenshot…..
Where Are they?
Your Solution is Not working for me….
Its giving error
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2008-11-07T15:23:49). Exception Message: Could not connect to mail server. (No such host is known). )
Hi Dave & All,
I want to implement a trigger that will send email using database mail whenever anybody creates a new database on a server without letting me know aobut it.Can anybody give me the detail to do it please?
Thanks
!!!!the posts are awesome here.
Marshall
Hi Dave & All
I configured database mail to send me emails when job fails.When the job fails and emial is sent to me on the subject box it says’job completed’.same thing appear if i configure database mail to send email when the job succeeds or completes.How to make the ‘subject’ to say ‘job failed’ when the condition was ‘send email when job fails’ and ‘job succeed’ when the condition is ‘send email when job succeeds’?
Thanks
Marshall
Hello, I need to create a database that will contain student’s grades for different subjects and different grades. Then I need to send each student their individual grades only. How can I achieve that?
Hello Pinal Dev
Thanx for ur this gr8 help.
Some days before i just heard abt Database mail.
I fired query in Google (thanx google :) )
I got ur article.
It works finly.
Just one silly question :
If i want to send mail with HTML tags so what to do for that ?
Thanx again to you and Monica also
Regards,
Saagar
Hi Pinal,
I got the solution for HTML Tags Mail format
Thnax
Regards,
saagar
Hi, I have tried setting up database mail (SQL SERVER 2005) before seeing your article, and it is the same thing basically that I did (your gmail example).
I get the error:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-12-01T07:01:53). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required.
I use this exact same account and settings for smtp in C#/ASP.NET and it works there.
Here are my configurations:
– enabled Database Mail stored procedures
– created a new account (smtp.gmail.com, port 587, checked SSL required, valid username, valid password)
– created a new profile that uses that account (public, default)
– send test email (valid email)…
Am I missing something? Do I need to manually add user to DatabaseMailUserRole? (somehow that role did not exist, I had to add it manually)
Do I need to setup an SMTP server in IIS?
Thanks,
Amy
I was having this same problem and didn’t find an answer. So, I ended up deleting the profile and mail account and starting over. Once I did that it started working. I think the problem could have been that I modified the original configuration of the mail account after it was setup.
– Good Luck! –
@Amy:
You don’t have to setup SMTP on IIS, unless you are planning to use it instead of GMail’s SMTP server.
If you do setup an SMTP server on IIS, then you’ve got to specify “localhost” instead of “smtp.gmail.com”.
-Nitin
Thanks Nitin,
So do you have any idea why it’s giving me an ‘authentication required’ error?
What about reading emails? Is it true that Microsoft will, or did, disable this functionality ?
If so this will be very Interesting logic… If you can’t secure it then kill it… Well…I hope I am wrong, I have an application that reads and process email in 2000 and hoped to upgrade… Any feedback?
How to create Scheduling of SQL Email?
I wish to have email generation from SQL Server daily at 0900 with particular data from database.
….
how to check the date in database & send the mail using c#
Hey Pinal
Can you set up the email so that it is not automatically being sent but rather being sent at a programmed time and date? and how can this be done for world time zones so that the email is set to arrive in your inbox at a particular time say 9am on the 10 January 2009 in your time zone?
Thanks
I just want to say that your work (not only on this topic) is great!!!!!
Hi everybody,
I configured the datamailfollowing the above steps…
when i am trying to send a test mail, i m getting following error…
please anybody can help me regarding this. i need to finish this task immediately.
Error message:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2009-02-05T10:52:07). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Authentication required).
Thanks
Mohan
Hi,
I want to send an SMS using this feature.
when i try to send SMS(9198xxxxxxxx@airtelkk.com) using webmail i’m getting the SMS clearly. but when i try the same using database mail am getting some junk value or may be the encrypted form.
How to overcome this..?
plz help……
Thanks in advance..
Pramod..
Saagar Wrote :
“——-”
Saagar
Hi Pinal,
I got the solution for HTML Tags Mail format
Thnax
Regards,
saagar
“——”
What is the solution? Please share.
is outlook is required for the database mail to be working..????