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
Hey, Pinal:
I think you should publish a similar article with the total GUI explanation given by Imran Mohammed. My attempt to config settings/permissions on a VISTA localhost had all kinds of blocking problems and I could never get passed “actively blocking” message.
But I followed Imran Mohammed’s clear explanation above and no problems!
Might be useful for other developers fighting with Vista Firewall, SMTP, etc.
Thanks!
How do you Receive email into SQL Server? If we get an email to support@ how can we set it up to receive into our SQL Server database?
Hi Pinal,
how are you?
I did that same as your showing. However, I cannot recieve the email in Inbox. The error in log is “…because of the mail server failure.” but the server did successfully pass in Database Mail Configureation Wizard.
Please tell me how can handle that issues.
Thank you,
Frank
Does anyone know if SQL Express 2008 has e-mail functionality? I do not see the “Database Mail” anywhere.
I know the full version has a wizard for this, but I am unable to locate or find documentation that supports e-mail with the Express version. Any assistance would be greatly appreciated.
Thank you.
Mark
@Mark A. Ross
Database Mail is only available in following Editions of 2008.
1. Enterprise Edition
2. standard Edition
3. Workgroup
4. Web
Express Edition does not have database mail feature.
Look under Manageability section for more details in below link
Link : https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017
Suggestion: Why dont you use developer Edition or Evaluation copy.
IM.
Hi Pinal Dave,
I have tried to Configure Database Mail in my SQL server 2005 with my gmail account, but it always shows
Mail queued.
It not sends the mail to receipent email account.
Can u help me out, where I am doing the mistake while configuring the database mail.
Thanks in advance,
Thank you guys Big time. Am sorted.
Greate articale, very helpful.
Good article. However, most applications will not be connecting to the server as SA. To send mail using sp_send_dbmail the account being used must be a member of DatabaseMailUserRole, Most application accounts will not be so if you have a stored procedure in anotherdatabase that wants to execute sp_send_dbmail you will get an error.
As yet I am not sure what the bet solution for this is. ie create a sendmail role and allow the sql accounts to be members of this and allow this role to execute sp_send_dbmail
Any one got any better ideas ?
Hello Sir,
I lost some record from tables and I don’t have any backup.
Is it possible to retrive that data? How? Please help me.
Sir
I am also unable to receive email on the address that i specified there . Can you Plz provide help ?
Hi Dave,
Awesome directions on setting up SQL Server Emails. Followed step by step instructions and now my email’s are working.
Thanks
Jeff
U r g8
Hi Dave
i would like to Thank you for getting know a new concept
But i got error while send mail after successfully finished 2 steps
Please let me know and tell me when we have to use database mail(give me a real time example )
once again thanks
The err msg
Msg 8145, Level 16, State 1, Procedure sp_send_dbmail, Line 0
@profilename is not a parameter for procedure sp_send_dbmail.
Wow nice article it is. It will be useful to all.
Thanks so much for this wonderful article!!! Its a very good explanation for whole setup with diagrams.
I learn’t a new thing in SQL Server today. Once again thanks to Pinal and Monica.
Can I configure Database Mail to work with a hotmail acclount ? Can you explain how to configure it, please ?
Hi Pinal
I would like to say thanx for providing detail infomation. It works great for me
Dear Pinal,
I am using MS Reporting Service 2008.
After excuting the report I want that the report should be
send to his supirior at that moment using as user email id to
his superior to suerior adderess in SSRS only.
Very difficult facing please give some idea or hint to solve this problem.
Thanks in Advance
Ramesh Sahoo
I too am facing same issue with SQL 2008. It seems the account works from application using SMTP account, but not in SQL 2008? Can any one give complete details for SQL 2008.
Thanks a lot!