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
Hi,
This is a really good article and I was able to configure and send email from MS SQL Server 2008 at first time.
Many thanks for your article and well explain step by step with screen shots.
Chamil.
I was getting the following error related to SMTP so plz help me in that out.
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 7 (2010-08-12T11:59:48). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for test@example.com). )
Hi Pinal,
I used your suggestions step by step, but while seeing the log , i found this error and the mail sending failed.
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-08-12T12:58:30). Exception Message: Cannot send mails to mail server. (Service not available, closing transmission channel. The server response was: 4.3.2 Service not available, closing transmission channel). )
”
can you please help me out in this regards,
Many thanks
To Sandip,
I had the same issue. I fixed it by deleting the profiles and recreating the same.
Try to delete the profile and create a new one with the same properties
Hello Pinal,
Thanks a million for this post, its got very useful for me.I used to run services and send email alerts manually.With this post I have automated 50% of my work.
I have created a nested stored procedure to send email alerts automatically based on procedure result.Is there anyway I can execute my stored procedure on schedule times using sql jobs or any other feature and please suggest.
I really appreciate your knowledge sharing even though you had a hectic work schedule,”Helping hands will never be alone”.
hi Pinal,
I have configured the DB mail , and job. I can send emails sucessfully, but is there any way to get the status of email sent.
As many times we used temp email address like abc@abc.com, and these emails will never be delivered, is ther any table which can tell me that the email sending is failed?
Though I am using the foloowing sql to get status of email,
SELECT SA.mailitem_id,SA.profile_id,SA.recipients,SA.copy_recipients,SA.blind_copy_recipients,SA.subject,SA.body,
SA.send_request_date,SA.send_request_user, SA.sent_status,SA.sent_date,SA.last_mod_date FROM msdb.dbo.sysmail_allitems SA
INNER JOIN msdb.dbo.sysmail_profile SP ON SA.Profile_Id = SP.Profile_Id
AND SP.Name =’ReminderProfile’
but it always shows ‘sent’ in sent_status column.
Any idea?
Very complete and helpful, thanks!
Hi,
After configuring mail account for data base mail i am getting following error.
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-09-03T13:22:43). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for ). )
Hi pinaldave,
Bhanu, How to schedule email , for database backups (like failure or success of database while backups are running)
Kind Regards
Bhanu
hello
Could you please help me with solving a task I have got from a client? I’m a very beginner and it’s first time when I have been asked to create anyhting like that. Thre is a request:
“When we install a new release of the application, or when we need to balance the SQL cluster to the other node, some application functionalities are malfunctioning (Database Error messages or similar).
Also, after a new release is installed, we need all current users to quit the application and restart it.
We need to find a way to warn all the current users that the application will go for maintenance in X (X must be configurable and may be zero for emergencies) minutes and, when this X minutes is elapsed, the application must quit automatically. During the maintenance period, the application should not start (with a nice message explaining why). ”
Thank you very much,
Thanks, I didn’t know about the msdb helper tables. That will help me to diagnose whether any emails are not being sent to proper email addresses. Pinal, I have enjoyed referring to your blog the past couple of years.
Hello Mr Pinal,
Thankyou for the screenshots on your website..
they are really very helpful.
I want to know that is there any way to automatically send mails from sql server 2008 with the details in the body of the mail and not as the attachment to the mail.
I would be really very helpful to me If there is some way to do this.
I want all the details like Name of database, Space allocated,Space used,Space free…etc etc in the body of the mail.
Please help…
Am getting the following error:- Please help me on it..
Date 12-10-2010 PM 3:16:24
Log Database Mail (Database Mail Log)
Log ID 42
Process ID 4216
Last Modified 12-10-2010 PM 3:16:24
Last Modified By PHX-RSS-PROD01\appuser
Message
1) Exception Information
===================
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: Could not retrieve item from the queue. Reason: the list of accounts which could send this mail is empty (probably due to the use of invalid profile).
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Controller.ICommand CreateSendMailCommand(Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DBSession)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
hello pinal and all the members
Well nice post. But i would like to know, what if my machine is not a XP.
In that case what need to done because here we are enable the Database Mail XPs parameter through the sp_configure.
hope for quick reply, its urgent
Thanks
Hello,
i need your help please. I have an SQl Server 2008 which must send Alerts to an Exchange 2007 User.
I have configured Database Mail to use Port 25 for sending EMails with TLS and Basic Authentification (Exchange Mailboxuser).
Computer-Certificates for the SQl Server and the Exchange Server where installed (on the Exchange Server SMTP-Service is Enabled for the Certificate, and the Subject Names are OK).
When I send a testmail i became the following Error:
10/19/2010 10:58:54,,Error,623,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-10-19T10:58:54). Exception Message: Cannot send mails to mail server. (Server does not support secure connections.).
),6732,307,,19.10.2010 10:58:54,sa
When I send a testmail without TLS it’s working.
Thanks Stefan
hi i set up everything but when i try to send myself a test email i get this in the log:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-10-14T14:01:11). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender).
)
can you help?
Hello,
now i have change the port to 587 and i bcame the following Errormessage:
10/20/2010 08:20:17,,Error,2092,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2010-10-20T08:20:17). Exception Message: Cannot send mails to mail server. (The remote certificate is invalid according to the validation procedure.).
),5012,1482,,20.10.2010 08:20:17,sa
Can anywhere help me?
Nice Boss. i got my desire
Thank you so much Dave.
As always, clear, concise, and it gets the job done!
Thanks Dave
Hi…
Dave can u give me in “.pdf” format
please