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,
I am getting different kind of error in sending database mail from SQL server 2005 which is not included in troubleshooting database mail.
I am getting the error as per the log file
“Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2009-06-01T11:03:23). Exception Message: Cannot send mails to mail server. (Command not implemented. The server response was: Error: command not implemented).
)”
Using the same SMTP server info and same steps I am able to send test mail from different servers. But in a particular server I am not able to send test database mail but I am able to send message through cmd prompt using telnet command.
Please advice any server setup needs to be checked.
Hi all,
Please help me to solve the problem when I send mail by Database Mail, I encounter error message:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2009-06-04T16:22:26). Exception Message: Could not connect to mail server. (A socket operation was attempted to an unreachable host 209.85.147.109:465). )
I used smtp.gmail.com on 465 port
@Dung Ding Tien.
Please use
Server name: smtp.gmail.com ( this is really important)
port no: 587 ( on many website this is given as 465 which is wrong, use 587 )
I have demonstrated an example above on the same page, please take a look at it.
~ IM.
@Imran Mohammed
Thank Imran Mohammed,
I also used port 587 as you said but I still got the error message.I did step by step to configure database mail as same as guide in the article and I took your demonstration.
If you can,please explain detail.
Hi Imran Mohammed,
I am using SQL Server 2005, i configured database mail and followed all the steps accordingly but got the following error in log file and unable to get a mail also. Could you please help me.
————————————————————————
The mail could not be sent to the recipients because of the mail
server failure. (Sending Mail using Account 1
(2009-06-10T18:10:07).
Exception Message: Could not connect to mail server.
(No such host is known).
Hi,
Thank you So much for u r efforts. I successfully implemented today.
THanks for u r effort Pinal,Monica,Imran..
Regards,
Hari.
I have setup Database Mail – I can right-click on it and send myself a test email message. I get this message correctly.
However, from a Job, I go to Notifications and select my operator (my own email) and select “When the job completes”. Then I execute this job and I never get an email!
What am I missing?
Thanks
– Gustavo
@Gustavo.
Did you configure your SQL Server Agent to use database mail profile when sending an email.
All Alerts or emails are sent by SQL Server Agent.
Go to SQL Server Agent Properties and select Database mail profile. After you make this change, Restart SQL Server Agent ( Remember. no need to restart SQL Server Service, Just Restart, SQL Server Agent thats it).
And then try again to send email. Should work, In case if it did not, let us know.
~ IM.
Where i will find SQL Server Agent Properties ??
Hi Pinal,
Great to see your post.. I was very new to databasemail but reading your blog, now m not…. :)
Thanks !!!
Hello,
I have some problem with MS SQL Server 2005 standard.
I dont use sql mail server service at all. but still in SQL Agent log it says following
Message
[355] The mail system failed to initialize; check configuration settings
Message
[260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. —> System.Data.SqlClient.SqlException: profile name is not valid
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
a)
After that it Halt the SQL server and then I need to restart sql server and agent.
@Imran,
Error :
Unable to start mail session
profile name is not valid
Please check SQL Server Agent properties. And see if SQL Server Agent is using either database mail or SQL Mail.
If SQL Server Agent says it is using database mail, then it will also show you what is the name that account is using
Then, go to database mail ( in SSMS, Object Explorer), click configure and check if that profile exists ( Some one might have deleted that profile from Database mail.
If SQL Server Agent uses SQL Mail, then check in OUT
If you are sure you are not using it, then go to Surface Area Configuration anc uncheck Database mail, saying SQL Server not to start this service when sql server instance starts.
Its just a wild guess.
~ IM.
Thanks for the article! I’ve referred to it several times. I was wondering… How would I grant someone permission to View Database Mail Log? So far research has said that I need to grant the role securityadmin, but there’s got to be a better way.
Every time i try to send a test mail it show me the following error..
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-07-22T13:26:53). Exception Message: Could not connect to mail server. (No such host is known). )
plese let me know if i m wrong some where.
i have provided followind details :
E-mail address: sql@adveture-works.com
Display name: SQL account
Reply e-mail : sql@adventure-works.com
server name: mail.adventure-works.com
hi pinal,
i have configured the db mail & operator to send notification when job fails.
but i am not getting any notifications.
I am using sql2008
Thanks
Pragnesh
@Pragnesh,
After you configure database mail. You should change SQL Server Agent Properties to use this new database mail profile you just created.
After making a change, Restart only SQL Server Agent.
Also, Check Database mail logs (Right Click Database Mail, click View logs) to fnd out what is the issue.
~ IM.
Inmran Thanks for quick reply.
I have configured as you told.
& it also shows notification email in the log.
but i did not received any email.
Pragnesh
Pinal, nice example of the mail configuration.
Is this working also with an Microsoft exchange server ?
Which is the configuration for this ?
Thanks in advance,
Kurt
Excellent article, i tried this working fine (tried in SQL Server 2008 Standard Edition).
1. I created a auto backup job.
2. Created a alert and operator.
Now i am receiving schedule mail
Thank you very much, saved a lot of time.
Kumar
Sir,
I have a job in sql server 2005 which executes at 11:00 am everyday. It executes a query and sends the query result to users.
Since the server is in a remote location, sometimes we are unable to get connection to the same.
If we are disconnected from the server at around 11:00 am, or for any other reason (powerfailure) the server shutsdown, how can we get the email on the next restart of the server?
Thanks a lot in advance for your kind help !
Hi Pinal & All
I m using sql server 2005 and i used the db mail facility of the sql server. i want to do auto mail means job to the employee for the wish of birthday for that i am using cursor but it doesnt work..
so how to use cursor for fetch the one by one email id of employee and send the mail to them..
if there are many employee born on same date then how can i send wish mail to all with the help of cursor and cursor is not working in job schedule pls help me…
in job it is use msdb sp like
USE msdb
GO
EXEC sp_send_dbmail @profile_name=’test’,
@recipients=’dhavalsanghvi2006@gmail.com’,
@subject=’Test message’,
@body=’This is the body of the test message.
Congrates Database Mail Received By you Successfully.’
above code is working in job schedule but when i m use of cursor id doesnt work..
Thanks,
Dhaval