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 guys,
I am using sql server 2008 and having using with the database mail config.
This is the following error I am getting when suing the xp_senddbmail with attachment
—————–
Msg 22051, Level 16, State 1, Line 0
The client connection security context could not be impersonated. Attaching files require an integrated client login
—————-
when I execute with SA it works fine with attachment
but when I execute it with another user, it works fine without the attachment, but with attachment its throwing that error
Please let me know what I am missing here,
thanks in advance
John
sorry I was fast and typed it wrong, i was using sp_send_dbmail
Anyone who is getting this message “The mail could not be sent to the recipients because of the mail server failure.”
telnet to your exchange server, if you can with the port number 25
Then make sure that antivirus is allow the email access. This is how I resolved my issue.
mike please tell me how to send an email using task scheduler server 2008 or vista. im trying to use gmail but the all i see is a box asking for the smtp server where i input smtp.gmail.com but there is no place to input the gmail user password. please help
Is database mail available in SQL 2008 web edition?
hi Dave,
I m have implemented a database mail system using sqlserver 2005, it will be like if a jobs fails in sqlserver 2000 the mail will be send from sqlserver 2005.will be glad if any suggestions given by u this is the blog were i have written all the steps in detail..
Nice article Maruf really appreciate.
To configure database mail if any of ur jobs in sqlserver 2000 fails using the facility of sqlserver 200
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘Job alert Account’,
@description = ‘Job Alert’,
@email_address=’MailID’,
@display_name = ‘Job Status Alert’,
@username=’From Mail_ID’,
@password=’password’,
@mailserver_name = ‘Ip address of the mail server’
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘Job alert’,
@description = ‘Job alert mail account’
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘Job alert’,
@account_name = ‘Job alert Account’,
@sequence_number = 1
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘Job alert’,
@principal_name = ‘public’,
@is_default = 1 ;
declare @body1 varchar(100)
set @body1 = ‘Server :’+ ‘ Email Alert ‘
EXEC msdb.dbo.sp_send_dbmail @recipients=’ To MailId’,
@subject = ‘Job Failed Mail Status’,
@body = @body1,
@body_format = ‘HTML’,
@query = ‘SELECT * from databasename.dbo.Temp_Job_Fail’ ,
@attach_query_result_as_file = 1 ;
Second step
Created two tables on 2000 and 2005 server —called–Temp_Job_Fail
Third step
Create a Link Server so that the data from the table(in sqlserver 2000) can be inserted into the table(in sqlserver 2005)
Fourth Step
Paste the below given query on last step or any step of the job as per the requirement of the each job on sqlserver 2000
insert into Temp_Job_Fail
select b.name,b.originating_server,a.run_date,a.message,a.step_name from msdb.dbo.sysjobhistory a,msdb.dbo.sysjobs b
where a.job_id = b.job_id and
a.message like ‘%Error%’ and
a.run_date = convert(varchar(10),getdate(),112)
and a.message not like ‘%DBCC printed error messages%’
(INSERT INTO [Linked Server].Databasename.DBO.Temp_Job_Fail—2005 server
SELECT * FROM Temp_Job_Fail — 2000 server)
Fifth Step
Create a trigger on server 2005— to run the Job on table Temp_Job_Fail
CREATE TRIGGER trg_Insert_Temp_Job_Fail ON Temp_Job_Fail AFTER INSERT AS
BEGIN
if (SELECT NAME from DATABASENAME.dbo.Temp_Job_Fail)is not null
begin
EXEC msdb.dbo.sp_start_job @job_name = ‘MailSendingForJobFailedin2000’
end
else
Print ‘Issue’
END
Sixth Step
Create a job on sqlserver 2005 which will send the mail.
On the First step of the job Copy this query
declare @body1 varchar(100)
set @body1 = ‘Server :’+ ‘ Email Alert ‘
EXEC msdb.dbo.sp_send_dbmail @recipients=’ To MailId’,
@subject = ‘Job Failed Mail Status in sqlserver 2000’,
@body = @body1,
@body_format = ‘HTML’,
@query = ‘SELECT * from databasename.dbo.Temp_Job_Fail’ ,—output of the query will be send in your mail
@attach_query_result_as_file = 1 ;
On the Second step copy this query
Truncate table DATABASENAME.dbo.Temp_Job_Fail
Hi All,
I’m getting the same msg even when i tried from gmail’s smtp and ven by my locahost.
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-04-08T12:07:16). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it
209.85.231.83:25). )
Hi dave,
I am sending mail with attachment from SQL server 2005.
this is giving me error as follows:
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2010-04-08T16:49:52). Exception Message: Cannot send mails to mail server. (Exceeded storage allocation. The server response was: 5.7.0 Our system detected an illegal attachment on your message. Please).
)
If I send the mail without attachment, it is sent successfully.
Please help.
Regards
Aakash
“exec sp_SMTPMail @SenderName=’me’, @SenderAddress=’me@somewhere.com’,
@RecipientName = ‘Someone’, @RecipientAddress = ‘someone@someplace.com’,
@Subject=’SQL Test’, @body=’Hello, this is a test email from SQL Server'”
While running this stored proc sql server getting stopped, should I change any configurations?
Hi Pinal,
We have been experiencing lots of unsent emails with this error message:
The operation has timed out. at System.Net.Mail.SmtpClient.Send
on Windows Server 2008 with SQL Server 2008
we found a known bug # [ https://support.microsoft.com/en-us/help/968834/fix-error-message-when-you-send-an-e-mail-by-using-database-mail-in-sq/ ]968834 https://support.microsoft.com/en-us/help/968834/fix-error-message-when-you-send-an-e-mail-by-using-database-mail-in-sq/ )
that would be fixed with a hot fix that IT is reluctant to run.
We have now set-up a new server Window Server 2008 R2 and want to know if you know if this known issue has been resolved in this version or if we still need to run the hot fix?
Any suggestions, thanks Linda
**IMPORTANT**
for all of you having the folowing ERROR:
******************************
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 10 (2010-05-31T15:12:25). Exception Message: Cannot send mails to mail server. (The operation has timed out.).)
******************************
It’s caused by a bug in Management Studio:
Use smtp.gmail.com for the server
port to 587 (Use port 587, port 465 lets you waiting)
Require secure connections checked
*Basic authentication*
username (use the full email address with @gmail.com)
password (RETYPE – the SSMS messes up the password so the autentication fails with port 587)
You may also want to read this…
https://support.microsoft.com/en-us/help/968834/fix-error-message-when-you-send-an-e-mail-by-using-database-mail-in-sq/
https://support.microsoft.com/en-us/help/968834/fix-error-message-when-you-send-an-e-mail-by-using-database-mail-in-sq/ )
Hi Dave,
Is there also a possibility to make your email more fancy by using different fonts or colors.
This could be helpfull because I create some reports to the management. The current body is currently flat text and it difficult to indicate some important items.
Thnks,
Kurt
pinaldave,
I received a email successfully in junk folder. But i want to receive in “Inbox” suppose the user are not aware of safe recipient list. Is it possible from SQL Server side?
Regards,
Siva
Great write-up; thanks. I’ve successfully set up Database Mail on SQL Server R2 Nov. CTP, and it is working via anonymous relay through Exchange 2010. I have both behind a firewall, and an anonymous relay connector in Exchange with the IP of the SQL box. The problem is that I have multiple IP addresses on the SQL box. For some reason, it seems to always be sending from just one of the IP addresses – the one that I have configured in the anonymous connector. However, I’m not confident it will always send from that single IP. Where can I control that to ensure all database mail is sent from a specific designated IP? The OS is 2008 Server R2 Enterprise.
Hi,
I am using SQL2008/64.
I can successfully send emails but I also want to receive them. XP_readmail is deprecated in the 64 bit version of SQL2008 and I cannot find anything that replaces it.
Does anyone know how to do this.
The requirement is for management to be able to ‘post’ comments against an SSRS report by clicking on a link and sending an email to the database. We are not using Sharepoint so the built in facility for that is not available.
Hi,
I Have configured DB mail in my server and we are getting mails from DB for the past few months without any issues. But nowadays we are getting mails with a ‘spam:’ notifications. I tried updating the Profile and account. But still facing the same issue.
Can you please help me to avoid this spam notification in the subject of a mail.
Hi,
I Have configured DB mail in my server and we are getting mails from DB for the past few months without any issues. But nowadays we are getting mails with a ‘spam:’ notifications. I tried updating the Profile and account. But still facing the same issue.
Can you please help me to avoid this spam notification in the subject of a mail.
Hi, I have done all the task , but i want to sent both sender and the from property of the mail which i want to send, my Concern is to the send the mail from a common E-mail address but on behalf of some one else email address, i can do this using MailMessage class in c#, Please give me some solution to do the same using db
Thanks
Pinal, do you have an example that would allow multilingual text in the email. I need to send Chinese/Japanese emails with both english and dbcs characters in the subject and body.
Dear All,
I tried the same configuration to set up my database mail but i can not send the mail , when i see the log file or history of database mail i got the below Error
Log Database Mail (Database Mail Log)
Log ID 94
Process ID 3000
Mail Item ID 25
Last Modified 5/31/2010 3:12:53 PM
Last Modified By sa
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 10 (2010-05-31T15:12:25). Exception Message: Cannot send mails to mail server. (The operation has timed out.).
)
Can you please help me out to sort out this issue… I will be great help from your side
my Mail Id is : [edited email address]
waiting for your reply…