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
Hello,
Its really Good. I appreciate Monica’s patience in getting all the pics.
Also, the moment you right click database mail, and you try to configure it, and this feature is off ( default setting ) then you will get a dialogue box saying do you want to on this setting…
As a learner I would also like to see where we will be using this useful feature of SQL Server 2005. The one good reason where we can use Database mail is to send alerts.
It would be good if you also add how to sent alerts using database mail.
1. Create operator,
2. In response to job, sent email to this operator ( this email will be sent by database mail).
I know I am asking too much, I still appreciate her for spending so much time, I use database mail every day, but I learnt some new things in this post.
Thanks Monica and Pinal Dave.
Imran.
this worked like a charm and thank you for providing such a great breakdown on how to do this. I have set up one of our servers without a hitch but another server, which is virtual gave me the following error:
*****************************************
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-11-19T08:34:02).
Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 …
Relaying denied. IP name possibly forged [10.1.
*****************************************
Is this because it is virtual or do you think there is something going on with the specific IP?
i am also facing the same error:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-11-19T08:34:02).
Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 …
Relaying denied. IP name possibly forged [10.1.
what could be the reason
I am getting the same error when I send email to outside of network. Any help?
This is really cool. The mail works. I am able to send mail in first attempt.
hi ,how did u did it ??? it doesnot works on mine same problem mail doesnot sent
yes all is done, but my mail is still queued not receaved
hi, thanks for the great document, is it possible for me to send automated (daily)e-mails that gets the results of different stored procedures?
Dear pinaldave
I m getting an exception after executing the procedure sp_send_dbmail as below
Cannot send mails to mail server. (The operation has timed out.). )
Exception Information =================== Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The Transaction not longer valid.
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/ )
i m also getting the same error…
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2011-05-15T17:47:17). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )
and tried the solution u hav given…but it didnt worked
plz do tell wht to do..???
aishwarya,
If you are using a gmail account in the settings, do as follows.
After the previous steps check the last one: Basic authentication – password – REENTER THE PASSWORD – the SSMS messes up the password so the autentication fails with port 587 if you don’t RETYPE it
Thanks Frank.. I was getting the Same Error and now its Fixed..
Nilesh,
Are you using SQL Server 2008? It works great for me.
Thanks Pinal.
Great work.
Good Work! This is very helpful post.
Imran you are correct. Monica good pics.
Mike Aaron – Microsoft MVP
As i’m newbie to sql server i started with this new feature of sql server 2005 (i.e. using SMTP not MAPI) i got stuck with few things as my sql server 2005 has the default settings.
firstly enable database stored procedures.
start – programs – microsoft sql server 2005 –
configuration tool – sql server surface area configuration – surface area configuration and features
the two more things what we have to keep these things in mind while configuring database mail:
* by default port for database mail is 25.
so open that port in the firewall.
* grant permissions to relay e-mail through SMTP
start – administrative tools- Internet Information Services – expand local computer – default SMTP virtual Server Properties
– thn click tab Access thn Relay – add –
then enter IP address – click ok
Now i guess it would definately work. please correct if i’m wrong.
Sandeep-MCTS
— using T-SQL
use msdb
declare @profile_id smallint
exec msdb.dbo.sysmail_add_profile_sp
@profile_name=’DATABASEMAIL’,
@description=’DATABASEMAIL’,
@profile_id=@profile_id output;
select @profile_id
–exec msdb.dbo.sysmail_help_profile_sp select * from msdb.dbo.sysmail_profile
declare @account_id int
execute msdb.dbo.sysmail_add_account_sp
@account_name=’sandeep’,
@email_address=’s.charaya@server.com’,
@display_name=’sandy’,
@description=’kuch bhi’,
@account_id=@account_id output;
select @account_id
/* to update
declare @account_id int
execute msdb.dbo.sysmail_update_account_sp
@account_id=1,
@account_name=’sandeep’,
@email_address=’s.charaya@server.com’,
@display_name=’ElixirCT’,
@description=’Sandeep Charaya’,
@replyto_address=’s.charaya@gmail.com’,
@mailserver_name=’server name’,
@mailserver_type=’SMTP’,
@port=25,
@username=null,
@password=null,
@use_default_credentials=1,
@enable_ssl=0;
exec sysmail_help_account_sp
*/
–select * from sysmail_account exec sysmail_help_account_sp
–exec sysmail_help_profile_sp exec sysmail_help_account_sp
/*
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_id=1,
@account_name=’sandeep’,
@sequence_number=1
–or
@account_id=1,
@profile_name=’DATABASEMAIL’,
@sequence_number=2
*/
–exec sysmail_help_profileaccount_sp
–make the profile public
exec sysmail_add_principalprofile_sp
@profile_name=’DATABASEMAIL’,
@profile_id=1,
@is_default=0,
@principal_name=’public’
–exec sysmail_help_principalprofile_sp
exec msdb.dbo.sp_send_dbmail
@profile_name=’DATABASEMAIL’,
@recipients=’s.charaya@gmail.com’,
@subject=’hello…test mail’,
@body=’hi,
h r u’
SELECT * FROM sysmail_allitems
i hope it works !!
Thank you so much. It worked like a charm.
Very useful queries and statements for understanding sp_send_dbmail in detail. Nice compilation.
Hi Pinal Dave,
Actually it’s not different much with SQL 2005, isn’t it :)
What I’ve written in my previous post –>
was only kind of shortcut so that I don’t have to open the wizard, and when I red your post, the wizard looked the same as SQL 2005 (I’m not yet explore SQL 2008 because my latest software version, Ms Dynamic SL, is still using sQL 2005)
Thank you for informing :)
Really a good post. I want to ask one thing, can we take backup of database using Job, and then send the backup as attatchment to a particular email ID using Database mail. This will provide a backup to the superadmin who is in fact a nontech person. My database is very crucial and have encrypted all the varchar column using 128 bit encryption. There is requirement of the highest degree of protection of data as well as database.
To achieve this the work to be done in sequence (as it think):
1. Create profile
2. Create job for Backup
3. Create job for sending mail.
Can you help me out how to achieve all these.
Regards,
Jitendra Gupta
Hi Pinal,
I have created a maintenance Plan for database and Log backup in SQL Server 2005 using Wizard. But it does not execute at the time specified. When I ran it mannually it gave error like:
“Execution failed. See the maintenance plan and SQL Server Agent job History logs for details.
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch.”
When I peeped into the Job History Log, there was nothing under the maintenance plan.
Please let me clear where, what I am missing.
Regards,
Jitendra Gupta
Excellent Post – thanks.
I successfully configured as above several days ago in a new production SQL 2005 cluster. My problem is that the Test function is not available for the SQL Agent configuration unless using SQL Mail AND the job notifications fail…?
The actual Database Mail configuration test works fine.
Any ideas?
Hello friends…
Great work by monica. I have done all the things mentioned by monica. But finally i am getting an error saying that
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-08-30T16:31:11). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )”
how to overcome this.
Thanks in advance
Regards
Zak
Hello Pinal,
How can we send mail in ibm db2/400 (database of as/400)?
Thanks and regards,
Himanshu
Hello Pinal and Zakeer,
Mr. Zakeer, i am getting the same error as u had.
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-08-30T16:31:11). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )”
how to overcome this.
Thanks in advance
Narayana
@ Narayan,
By default we will use port 25 to configure database mail, which is usually blocked by windows firewall for security reason,
try the following.
1. Disable firewall and test if databasemail sends email.
2. Make an exception in the windows firewall.
Or this also happens when you use wrong port numbers,
Just as an example use these values when setting up a database mail feature: You need to have a gmail account, which can be created free if you go to gmail.com, you can send email using gmail service free of cost :) So you can try this example and it works perfect.
In database mail, first create profile, and when you create an account, use these values.
Give Account name: SQL Server Alerts System
Description: XYZ
Outgoing mail Server ( SMTP)
email id: youremailid#gmail.com ( should have gmail.com)
Display Name: SQL Server
Reply email: can leave blank
Server name: smtp.gmail.com ( this is really important)
port no: 587 ( on many website this is given as 465 which is wrong, use 587 )
Check : This server requires a secure connection
Check Basic Authentication
username: youremailid@gmail.com ( should have gmail.com)
password: ( top secret , dont display ) … ;)
confirm password: confirm your password
click next and also you need to make it default public profile.
After you do this, you also have to change SQL Server Agent properties, in Properties click alerts system and then select database mail.
MOST IMRORTANT when you make changes, to SQL SERVER AGENT properties, restart only SQL SERVER AGENT.
right click SQL Server Agent and click restart, and then test your database mail.
to test, right click database mail and click send test mail… VERY IMPORTANT ( Select right profile/account from drop down list), put any email id and click send test email. click ok.
Right click database mail, select view database mail logs keep refreshing … to see if any error message had occured. mean while look in your inbox if you recieved your email.
In few days I will take screen shots on how to configure database mail, taking a live example.
All with GUI’s no scripts :)
Hope this helps.
Imran.
Hi Imran Mohammed I am Trying the same thing now.but i can unable to send mail.In Log File Viewer-Message- Its Showing this Error (04/09/2010 06:11:57,,Error,13,The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-04-09T06:11:57). 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. 8sm165107yxg.67).),3480,6,,4/9/2010 6:11:57 AM,sa)
Plz Help me regarding this…
@Nirmal
Looks like this is a Authentication issue or port no issue.
Authentication Issue:
—————————-
Please make sure you have put the information as explained below:
Server name: smtp.gmail.com ( this is really important)
port no: 587 ( on many website this is given as 465 which is wrong, use 587 )
Check : This server requires a secure connection
Check Basic Authentication
username: youremailid@gmail.com ( should have gmail.com)
password: XXXXXXXXXXXXX
confirm password: confirm your password
Port No Issue:
——————–
Make sure port 587 is opened in your machine if you are following above example. If you are using any other port number (usually its port 25) to set up database mail in your office, make sure you speak with your security team and ask them to unblock port 25. Once they unblock port 25, your databasemail will work.
Network Connectivity:
—————————–
Also check network connectivity from your machine, if you can connect to internet from your machine on which you are testing your database mail. To test, do the following
Start- Run- type cmd – Hit Enter
Type ping gmail.com -t and then hit enter.
If output displays as Request timed out, it actually means there is network connectivity problem. Otherwise, your network connectivity is good.
~ IM.
Very thanks Imran…Now it works properly…..
@@Imran Mohammed
Thank you Soo Much… DB mail is working for me without any issues…
thanks imran sir;
your post(April 9, 2010 at 7:31 am ) solved my all problem.
now i have send mail sucessfully.
thanks Imran Mohammed, it works grate …..
after configuring the datamail, when i send the test mail using send test mail tab..i am getting following error.
exception mesgae:can not send mails to mail server, mailbox is unavailble.
server response was 5.7.1;client does nto ahve permssions to send as this sender,
am using the sql service acoutn.
how do i knwo, wthether my account is having mailbox or not or do we need mailbox user account.
Regards
Srinivas
Hi Srinivas
My guess is, that you are not allowed to relay on the (SMTP) server you are using. Make sure the SQL servers IP address is allowed to relay on the SMTP/mail server – og use Basic Authentication with a username/password.
Best regards
Jakob H. Heidelberg
MVP:Enterprise Security
Hi thanks, a lot for posting solution for sending mail.
Can anyone help me with this?
the following script which returns the count of cases in the support queue and the date/time of the oldest case. Then it emails me the stats.
It works fine when I run from the query window, but when I run as a step in the job, the @body1 assignment is lost and I have no body in my email. Do you have any ideas about why the job appears to lose the variable assignment?
declare @CasesInSupportQueueCount varchar(10)
declare @OldestCaseInSupportQueue DATETIME
declare @body1 varchar(100)
declare @CRLF char(2)
SET @CasesInSupportQueueCount = (SELECT COUNT (qi.queueitemid) AS CasesInSupportQueueCount
FROM FilteredQueueItem as qi
JOIN FilteredQueue as q
ON qi.queueid = q.queueid
WHERE q.Name = ‘Support’ AND qi.objecttypecodename = ‘Case’)
SET @OldestCaseInSupportQueue = (SELECT MIN (qi.createdon)
FROM FilteredQueueItem as qi
JOIN FilteredQueue as q
ON qi.queueid = q.queueid
WHERE q.Name = ‘Support’ AND qi.objecttypecodename = ‘Case’)
SET @CRLF = CHAR(13) + CHAR(10)
SET @body1 = ‘Queue Statistics: ‘ + @CRLF +
‘Queue Count: ‘ + @CasesInSupportQueueCount + @CRLF +
‘Earliest in Queue: ‘ + CAST(@OldestCaseInSupportQueue AS varchar(30))
exec msdb.dbo.sp_send_dbmail
@recipients = ‘Email@email.com’,
@subject = ‘CRM QUEUE STATISTICS’ ,
@body = @body1,
@body_format = ‘TEXT’,
@profile_name = ‘Admin’
RESULTS WHEN SCRIPT IS EXECUTED FROM QUERY ANALYZER WINDOW:
Queue Statistics:
Queue Count: 7
Earliest in Queue: Otc 9 2008 1:59PM
RESULTS WHEN RUN AS JOB. (There is only one step in my job.)
Email sent but no body is display
IF I CHANGE
@body = @body1,
TO
@body = ‘just a test’,
On the email I get:
Just a test
Thanks
Thanks Pinal , Monica.
Good Article .Really helpful .
Rgds,
Hari
hello, i followed the procedure and i am able to send email from the server successfully.
HOWEVER – i cant get the Jobs to use the db mail feature – when i go to Job Properties — Notifications, check the E-mail box the drop down is EMPTY ! nothing to select from although i have the db email profile setup and working.
any ideas? (i restarted the agent, didnt help)
Those drop downs are for “operators” — add them below the sql server agent area. Basically, a “profile” is used to send mail using an “account” that sends to a “operator”…
good luck!