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
I’ve tried to set up an email alert using Database mail as described. I’m using Gmail, port 587, but I’m getting ‘No such host is known’ Tried using port 25 and indeed 465, to no avail.
Hi Pinal,
It made my job easy. Thanks for this post.
I can see mails in my inbox… wooo….
Hi guys,
I am having a problem with SQL 2005 sending database mail.
The error I get is as follows:
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 10 (2010-06-08T11:27:52). Exception Message: Cannot send mails to mail server. (An invalid IP address was specified.).
)
Thank you very much! Your article is very helpful.
Getting an issue in database mail configuration in sql server 2005:
When using server name: smtp.gmail.com and port number:587 getting the following log:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2010-06-17T12:14:25). 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 Must issue a STARTTLS command first. t1sm7767720rvl.21). )
When using server name: smtp.gmail.com and port number:587 getting the following log by selecting “The server requires secure connection”:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2010-06-17T12:47:21). 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. Learn more at ). )
Please give suggestion what should I do….
Advance thanks.
hi pinal,
i am unable to send email .i am using port 25.and i followed the steps want you have said.but even we are getting error .
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 11 (2010-06-21T13:45:14). Exception Message: Cannot send mails to mail server. (Client does not have permission to submit mail to this server. The server response was: 5.7.3 Client was not authenticated.).
)
Hi,
I have tried so many times with database mail but I am getting an error
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-06-22T17:37:58). Exception Message: Could not connect to mail server. (No such host is known). )”
I used smtp.gmail.com as server name and port no has 587 but its not working what could be the problem. Even I queryed the “sysmail_sentitems” no records is available
Can you solve this problem at earliest .
Regards
Allah Baksh. M
I have the developer edition and trying to use smtp.live.com but it’s not working for me.
secondary I also want to prioritize the emails sent over others.
will using @importance=’High’ help send email A over email B
Thanks
I am using SQL server 2008, configured emails and working fine.
I sent an email to wrong email address; when exchange server/smtp failed to send specific email address. Failed notificaton email will come to the emailID used in the profile.
The goal to read the failed notification email or exchange log and update mail status…in another table.
Please let me know if any one has an idea.
Hi,
This is absolutely helpful for me , thankx uploader
Database mail is fantastic facility provided by SQL Server.
Nimesh
Hi
Thank you very very Much
Worked well!
Thanks!
how to connect mail server localhost port “25″
for database mail?
Thanks for the post it works I have a question on editing
the email works fine
now as you look on the bottom i need there to be a space better the body of the text and the query results
is there a way to do that? also is there a way to remove the script after the query that says “#of rows affected” thanks
All attempts to charge the credit card on file for the following EZ Pay Customers has failed.
Please contact the customer, update the card on file and charge the balance to the new card.
LOCATION CUSTOMER ACCOUNT CUSTOMER_NAME PHONE
———- ————— ————— —————————— ——————–
7340 7340A0003935 5AD07 DANA BAKER 6317154814
7340 734000A04385 5ER22 RAY MAY 6314511250
(2 rows affected)
@Dhara,
Question: I need there to be a space better the body of the text and the query results
It should be very simple, you just add one more line to your body text like a blank line and it will appear in email body text.
Question: Is there a way to remove the script after the query that says “#of rows affected”
Yes, In your script at the begining of the procedure, use SET NOCOUNT ON
If you add above 3 words to your script, you will not get rowcounts information displayed in query results.
If this information does not answer your question, please share with us your SQL Statement.
~Peace.
thanks i had it figured out by the email you replied :)
Hi All,
I tried from gmail’s smtp.
Can anyone figure it out ???
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-07-30T12:02:56). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond [IP removed]). )
Changed my SMTP Client and after that it z working.
Hi,
PLEASE HELP ME its URGENT……
I configured and i received successfully mail to yahoo, gmail and hotmail. But expect its not working for my domain for Example: info@selva.com this is one of my domain mail id. i cant receive the mail to this particular domain mail id, Do have any idea regarding the same. Please help me ASAP.
Regards
Selva Shankar
How can i send mail from a database other than the MSDB database that the Database Mail uses? If i try and use another DB with my send mail script it says certain Stored Procs are missing. What do i need to add to get it working from another DB?
step1) Enable mails on your server level.
Step2) Define profile which it will be using to send mail.
Step3) send the test mail to your id using the profile you configured in step2.
Step4) If step3 worked, configure the progile in job notification or alerts.
I have application that needs to send mail alert to my clients prior to the day of their schedule .I have never done this before .How do i configure may sql server to do this.These are the fields i ve in that table.
CustomerName,TravellingDate.I will appreciate it if you can guide me through this precess.Thanks.