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, My emails sending option is working fine. But all the emails sending from sql server is going to junk folder in OutLook . This is same for both @body_format = ‘TEXT’, and ‘HTML’. What can be the reason & how do i overcome this?
Hi Pinal,
Can we send multiple resultsets (2 different query resultsets ) as multiple mail attachments by useing sp_send_dbmail procedure? Is there any option ?
Thanks for article.
With the help of your article i am able to send mail.
But how can i read mail in sql 2005
Please help me.
thanks again.
thanks for posted nice article,i done this as perfect but i can send single mail only i can’t send multiple mails at a time ,I have to write sp like
use msdb
GO
EXEC sp_send_dbmail @profile_name=’Manojprabakaran’,
@recipients=N’vadivelkarthick@gmail.com,manoj@icegen.net,karthick@icegen.net’,
@subject=’Test message’,
@body=’This is the body of the test message.
Congrates Database Mail Received By you Successfully.’
but mail would be send only first mail id,not an other …help me
Great stuff! Thanks very much
We are using 465 as Port. I tried Telnet,It is working.
I restart the agent also but still same error
Exception Message: Could not connect to mail server (Mail Server Failure)
Plz Help Its Urgent
Could you please re-upload the first three images.
Thanks.
‘PinalProfile’ values stored in database? If so, can you please share those table(s). I want to dynamically change the values of these tables.
the code below works perfect when executed in a query window, and im receiving the email
EXEC msdb..sp_send_dbmail @profile_name=’TestProfile’,
@recipients=’me@company.com.au’,
@subject=’Test message’,
@body=’This is the body of the test message.
Congrates Database Mail Received By you Successfully.’
go
however the same code used in a trigger, show that email is queued and it is logged in sysmail_mailitems, plus all fields are exactly the same. but i don’t receive the email
Please help me how to SEND message to speified e-mail address retriveing e-mail address from textbox in vb.net 2008
Hi All,
I have 3 servers when I send the data as CSV data, everything is working with one server the other 2 serveres data is not organized and scattered what could be the reason.
thank you
Hi Pinal,
This was a great help configuring on SQL Server 2008 R2. But I had to execute the following before Step 2, otherwise step 2 generates an error.
— Reset the “allow updates” setting to the recommended 0
sp_configure ‘allow updates’,0;
go
reconfigure with override
go
Cheers Manny.
clear and straight forward document, thank you
Good document, thank you.
Hi Pinal,
Is there a way call the stored procedure but with “From” as one of the parameter? We would like to have the email to be sent from various email address captured from the web front end text box.
Meanwhile, is there a way to resolve the attachment size limit 1mb issue?
i have Database mail working fine on my SQL but was looking to send calendar files (.ics) files as well via email – does anyone know of any code i can use in my stored procedure to send ics files?
Thank you for this article! I found it to be very useful.
Great! Pinal. It helped a lot .
Thanks Pinal…this article exactly provided me proper steps which I was looking out for..
Hi,
I am facing a problem executing a SQL query with sp_send_dbmail. The query is working perfectly fine without sp_send_dbmail.
This is not an issue with database mail as other queries are working fine.
Could anyone please advise me where to check the error w.r.t sp_send_dbmail
Thanks