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
Thank you this worked for me.
hey there, I have setup the Mail database but I am getting no alerts, when I send a test email – no probs, I receive into my Outlook, but when the jobs run, nothing. I even manually executed one of the Maintenance plans, and clicked Report–>Send report as Email and I get “No application is associated with the specified file for this operation”. Am I missing something
Dear Pinal,
I have configure each and everything but i dint fird Store Procedure “sp_send_dbmail”. So please let me know according to this.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure ‘sp_send_dbmail’.
Hi ,
Thanks for share nice Tutorial with us and itsrealy help full,
Im using a sybase as a DB with SQL and i need to send a mail to my customers geting some selected feilds from db it think i can use this same way for that as well ..
Thanks! It worked very nice..
Newletter Subscription:
Dear Sir,
My website has been hosted on Godaddy’s server.
Now i’ve to add one more functionality that is “sending automatic emails from a database”
on weekly basis. Is it possible?
If yes please help me.
Hi pinal dave
i am sending email from sql server 2005.
database mail configuration details set as :
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-11-16T10:34:13). 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. py4sm8247159pbb.33 – gsmtp).
)
Hi,
I am trying to send mails from sql server. I am trying to send mail with a query. I using gmail smtp with port 465. Always i am getting this error: Exception Message: Cannot send mails to mail server. (The operation has timed out.).)
When i use port 587 i get the below error
Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 74.125.129.109:587).
Please help me!!!!!!
———————————-
Please Follow Below Step:-
———————————-
Server Name :- smtp.gmail.com
Port Number :- 587
SSL :- Yes (Checked)
——————————-
SMTP Authentication :-
——————————-
Basic Authentication :- Yes (ON)
User Name :- your gmail ID
Password :- gmail password
Confirm Passowrd :- gmail password
—————————————
Configure System Parameter :-
—————————————
Account Retry Attemps :- 1
Account Retry Delay (Seconds) :- 5000
Maximum File Size (Bites) :- 1000000
Prohibited Attachment File Extensions :- exe,dll,vbs,js,com,bat
Database Mail Executable Minimum Lifetime (seconds) :- 600
Logging Level :- Extended
Enjoy !! Hope This Will Work !!
Ramesh Chandra
I want to give credits to Pinal Dave for posting this, great work!!. However i encounter a little problem like socket error. Anyway i made a blog which tackles about socket error issue. I hope it would be useful.
When I try to send test mail it gives me error like this,:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2013-12-19T12:05:38). 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. tn8sm2728017bkb.16 – gsmtp).
)
could you please help me,
this is my Email : mohebi@ideaco .ir
many thanks
Is it possible to Sending DB mail in SQL Server developer Edition..?,
Hi All,
Can some one tell me how to send automatically daily at particular time along with data from table to multiple people.
I know to automatically send mail create a job & schedule that, but i want to know how to add data dynamically from table to the mail.
If someone know, please tell.
Thanks
Dave you are my hero! I find your site many times very helpfull. Thanks.
Hi,
I have set up Database Mail. But when I am trying to send an email I am getting below error.
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2014-04-14T10:41:46). Exception Message: Cannot send mails to mail server. (The remote certificate is invalid according to the validation procedure.).
)
Please guide me what to do???
Thanks in Advance.
How to send sms via sqlserver 2008 . Guys is there any option ? if its there please reply .
Hi Pinal,
I am getting an error from DBMAIL.
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-05-19T05:01:04). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).
can you please help me on this.
Thanks,
Sree
HI Pinal,
I am Dhaval sheth,
I able to send mail and attachment also but in attachment only 20 column sql will take i am using SQL SERVER 2008 r2.
my issue is i want to send my report and report have more them 20 column.
plz help.
— Regards.
Dhaval Sheth.
sir i create the profile and send the mail to receipt but The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-06-23T15:00:18). 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 ). )
HI,
How can i send bulk emails of count 20000 users for a downtime using sql?
Tried: run the sql mail script in batches of 700 after 4000 emails , it is failing.
Can you please suggest if you have any idea?
DatabaseMail process is shutting down
Can you please tell me how can i correct this issue?…all the notifications should run on a particular date, as the list is very huge it is failing after certain point of time in this case after 4000 mails in batches of 700
use below line after each batches and make batch of 500 mail ids
WAITFOR DELAY ’00:00:05′; —- Wait 5 seconds
I have already implemented the wait for delay…but it is not for the exact intervals as the time where it is working is dynamic
can u send me sample qry so i can check
Is it resolved. I am facing same issue.
Hello,
I want to send query result as a table. How can i implament html or is there an another way?
create PROCEDURE [dbo].[HTML_MAILS]
AS
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
DECLARE @Subject_ varchar(1000)
Begin
CREATE TABLE #Temp
(
[Srno] [varchar](20),
[Description] [varchar](128),
[Total] [decimal](18,2)
)
INSERT INTO #Temp
SELECT [Srno] ,[Description],[Total] FROM tbl_contact_details
SET @xml = CAST(( SELECT [Srno] AS ‘td’,”,[Description] AS ‘td’,”,[Total] AS ‘td’,”
FROM #Temp
FOR XML PATH(‘tr’), ELEMENTS ) AS NVARCHAR(MAX))
SET @body =’
Dear Team,
Please find below detail from agility system.
Policy details for the Day
Sr No.
Description
Total Count
‘
SET @body = @body + @xml +’
This is an auto generated mail, Do not reply to this mail as mails to this id are not monitored. Please write to application.support@libertyvideocon.com for queries/assistance.
‘
—————————————————————————————————————————————————-
set @Subject_= ‘HTML report for the Day ‘ + CONVERT(varchar(11),getdate()-1, 113)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Reports’,
@body = @body,
@body_format =’HTML’,
@recipients = ‘Dhaval.Sheth@GMAIL.COM’,
@subject = @Subject_ ;
drop table #Temp
I want to send query result as a jobs like(failed and success jobs) . How can I implement html
please send me the query(then execute the query output in mail)
you can refer Balmukund’s blog to get query results in HTML format using XML PATH.
https://sqlserver-help.com/2014/11/12/script-setup-email-notification-when-blocking-occurs/