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
@katheesh kumar
STMP uses a port to send data from your computer to mail server. Just like SQL Server uses port 1433 (default).
STMP for gmail server is 587.
Default port for SMTP is 25. Usually in many companies they block all port that are not needed. It could be possible that port 25 has been unblocked and that is why you could send email through your office mail server and port 587 is blocked that is why you cannot send email using gmail server.
Solution, make exception in Firewall to unblock 587 Or Turn off firewall and test sending an email using gmail mail server, if it goes through. Then it is confirmed that problem is with Firewall blocking. Don’t forget to turn on your Firewall after testing is completed.
~ IM.
@Octavian
Thanks for the reply.
Do you have any idea why the email is not going through some time?. It works one day , next day it fails. we have a bunch of jobs and each send emails. some of the mails have no problem but some have . this Database mail has been working for about 2 years without any issues. all of a sudden this issue happened. (Relaying not allowed error..) We have not changed anything on the SQl server side. Do you think it is the problem with third party mail server?
congrats to all and especially Pinal, Monica and Imran.
However, although i followed Imran’s instructions regarding gmail’s SMTP configuration, i receive the message:
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-11-29T02:28:02). 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. 3sm6267711fge.15).
)”
I’d be grateful for any suggestions
Best regards
Hi all,
i have configured successfully database mail. and i have gone step by as pinal has guied above.
i have executed :
EXEC sp_send_dbmail @profile_name=’Faiz’,
@recipients=’info@geteternal.com’,
@subject=’Test message’,
@body=’This is the body of the test message.
Congrates Database Mail Received By you Successfully.’
but i m getting error below : —
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid
please help me.
Thanks
WORKED IN FIRST INSTANCE – GOOD JOB
THANKS
Pls how can get this Db mail
thanks inanticipation
pls how can i step up the SQL SEVER ON MY SYSYTEM
@mario
Are you looking for a download link? If so, which edition?
SQL Server 2008 Express is available here: https://www.microsoft.com/en-us/sql-server/sql-server-2016
Thanks for the useful information, Cheers
Dear,
i got this error message:
“could not load Dll xpsmtp80.dll or one of the dll is refrences .reason 193”
please give the short solution.
thanks with /mohan
Dear Pinal/Monica,
I have been trying this since days. And was done in one shot after I went through this solution.
That was a real helpful one.
-Malaya
Hi set up email (sql server 2008) OK.
However some get through and some don’t.
At this point, if can not figure out how to fix it,
how do you tell it to resend?
select sent_date,mailitem_id,profile_id,sent_account_id,sent_status from sysmail_allitems
where sent_date > ‘2009-12-15’
sent_date mailitem_id profile_id sent_account_id sent_status
2009-12-15 04:44:41.000 51 1 1 sent
2009-12-15 04:45:36.000 52 1 NULL failed
2009-12-15 04:45:51.000 53 1 NULL failed
2009-12-15 05:00:08.000 54 1 1 sent
Great article, Pinal. Do you know if there are system sprocs that return the email profiles and accounts? I’d like to avoid using the Database Mail wizard if possible.
Cheers,
Gerry
i have created a SP for sending mail.
if the all mail id is correct it send mail properly, but if any mail id is wrong or it set to auto reply.
This SP send mail twice to other correct mail ids.Please give me the solution.
Is there any another way to send mail?
ALTER PROCEDURE [dbo].[spSendMail_DailyReport]
AS
BEGIN
SET NOCOUNT ON;
declare @count int
declare @Query varchar(1000)
declare @SubDate varchar(100)
Declare @cmd nvarchar(4000)
declare @Error varchar(100)
declare @ErrMsg varchar(1000)
SET @ErrMsg = ”
DECLARE @tableHTML NVARCHAR(MAX)
IF @Error = ” or @Error is null
BEGIN
IF @count != 0
BEGIN
SET @tableHTML =
N’ Daily Report’ +
N” +
N’TransactionDateTransactionId’ +
N’SenderidSubmittedTotalUploaded’ +
CAST ( ( SELECT td = convert(varchar,BM.SentOn,103), ”,
td = BM.TransactionId, ”,
td = BM.Senderid, ”,
td = Count(*), ”,
td = Count(*)
from MakerChecker.dbo.BulkTransactionDetailsMAINTABLE BM
group by BM.TransactionId,convert(varchar,BM.SentOn,103),BM.Senderid
order by BM.TransactionId
FOR XML PATH(‘tr’), TYPE
) AS NVARCHAR(MAX) ) +
N” ;
print @tableHTML
–SET @tableHTML = ‘Test Message For Daily Log please ignore it’
END
END
ELSE
BEGIN
SET @tableHTML = ‘job is throwing error please check and Error no. is =’ +@Error+’ and ErrMessage is = ‘+@ErrMsg
END
————————– For Mail Sending ——————————
IF @count != 0
BEGIN
SET @SubDate = ‘Daily Log for ‘ + convert(varchar,getdate(),103)
–SET @Query = ‘master.dbo.xp_cmdshell @cmd = ”bcp “select convert(varchar,BM.SentOn,103) AS TransactionDate,BM.TransactionId,BM.Senderid,Count(*) as Submitted,Count(*) AS TotalUploaded from MakerChecker.dbo.BulkTransactionDetailsMAINTABLE BM group by BM.TransactionId,convert(varchar,BM.SentOn,103),BM.Senderid order by BM.TransactionId” queryout “D:\ETHold.csv” -U sa -P sa123 -c -t -S”’
declare @FileName varchar(100)
SET @FileName = ‘DailyLog_’ + convert(varchar,getdate(),104) + ‘.xls’
DECLARE @q VARCHAR(MAX)
SELECT @q = ‘SELECT ”’ + @tableHTML +””
SET @Query = ‘SET NOCOUNT ON;’+@q+ ‘SET NOCOUNT OFF;’
— print ‘mail’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘alerts ‘,
@recipients = ‘yogendra.mishra@bsmart.in’,
@copy_recipients= ‘darshan1.thacker@bsmart.in’,
@subject = @SubDate,
@body = @tableHTML,
@body_format = ‘html’ ,
@query_result_header = 1;
END
else
BEGIN
print ‘no data for mail sending’
END
————————————————————————-
SET NOCOUNT OFF;
END
Thanks Pinaldave.
It is working.
Hi,
I have a problem. Can you help me?
I want to send a mail using SQL server 2005 in every 12 hrs.
The attachment will be the result of a select statement in this email.
I don’t know how I will send it by sql server 2005.
Hi Rakesh,
In SQL Server 2005 to send mails, first you need to configure the database mail and create a mail profile. Then you can use the sp_send_dbmail stored procedure as below:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘mail profile name here’,
@recipients = ‘recipients email-id here’,
@query = ‘T-sql query here which result would be sent as attachment’ ,
@subject = ‘subject of email here’,
@attach_query_result_as_file = 1
Regards,
Pinal Dave
Hi pinal,
quiet good ..
but i need a small help..like i need to send a notification mail whenever my job is stopped or failed..
could u plz help me on this
Hello Chandu,
You can send notification on the failure of a job step by adding a job stepin job and call it on “On Failure” of other steps.
But SQL Server does not have any feature to identify stopped, hanged or slow running job. To identify such jobs you can use the code from following blog:
You can customized this code according to your defination of slow running jobs.
Regards,
Pinal Dave
We have .net and asp applications sending mails using Jmail.smtp. If I enable Database mail ( Sql 2005 ), will it break the Jmail?
I have created Database mail alert in an job while the job is executing the mail is send to the queue.
When we check the log, it is not showing that Database mail started and shout down.
When we execute database.exe file then it delivering the mail to the recepient. Can anyone reply why this problem is existing and solution for this?????
Nice article Pinal…Keep rocking
Regards,
Venkatesan Prabu .J