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,
I need one help from this community.
I do have one select query to be run in mydatabase [ select ewaittime from ewait with (nolock)
where efolderid = (select top 1 efolderid
from efolder with (nolock)
where eMapName = ‘ABC’
order by efolderid desc)
and eActionName = ‘Process XYZ table’ ] which will return one datetime.
I need to compare this time with the system time and if querytime < system time then one email should go to a paritcular recipients from the system.
What is the datatype of ewaittime?
Great Job here !
I am looking for SMS service, i mean i want to send SMS form database as You’r send the e-mails.
Please look for any possibilities.
thanking in advance
bye
Hi all,
I am getting an error from SQL Server 2008 while sending multiple mails.
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-11-24T08:23:16). Exception Message: Cannot send mails to mail server. (Insufficient system storage. The server response was: 4.3.1 Insufficient system resources).
)
I am getting this error if more than 10 mail sends together.
thank you,
Kumar
Hello Sir,
How to send SP in a JOB
In SQL2005
Thanks
Hi Pina,
Iam working on a Issue but could not resolve it ,I need advice from you.
I handle nearly 20 physical servers on which Sql server 2000 are installed ,I would like to get an alert to my mail from sql server when a server gets gown.
My thought is like this one primary sql server should ping to another servers for every 2 or 3 hours regularly and if it could not connect to the server, it should send automatically the mail.
for this is there any way to handle by maintence plans or TSQl or any other tool or software?
I need the information whether this possible or not as soon as possible
Thanks in advance
The tutorial was very helpful and easy to follow. Thanks for all the hardwork in putting it together.
Hi Pinal & Others,
First of all I would like to Thank you all for posting such a wonderful post.
Someone could guide me in sending an email notification within an SQL JOB (email as one of the steps).
I have created a profile for it and have configured a DB mail also. but no use.
Thanks,
Suresh.
Hi,
I have set up my mail and was able to send mail message sucessfully, but the issue is while tring to send using the script below
EXEC sp_send_dbmail @profile_name =’UPS Profile’,
@recipients= ‘XXXX’,
@body = ‘The stored procedure finished successfully.’,
@subject = ‘Success Message’ ;
I get a output message as “Mail queued.”
and to check the status SELECT * FROM msdb.dbo.sysmail_allitems shows that all items are sent, but I haven’t received any emails.
I have also asked my system administrator to check for any email, he did not find any mails or error messages.
Please help…
I must also send a file as result of output from stored procedure using this.
There are some things you need to set up before database mail will work.
Hi All,
I succesfull set Database and c/d set mail executing an sp whose output needs to be send as Mail, body, I do receive mail, but with blank body.
Is there anything i need to change to increase email body contect size, or else it this related with Network issue.
Please advice,
Thanks,
Nagraj.
It is impossible to guess why it’s not working for you, Nagraj. Post code.
I have the job as well as the email notifications working for fullbackup and differential backup and I added the transaction log backup but I do not see that edited to the previous email. Any clue why this is an issue? The SQLAgent has been restarted but that did not solve the issue.
When I ran this,
SELECT * FROM dbo.sysmail_allitems
order by send_request_date desc;
The status shows, ‘sent’. But why don’t I get an email?
Should the value of @recipients be my email address?
Hi!
Any one knows how to add two HTML tables on the same Sql Email?
i have on the first table
date
total 1
total 2
second table
Name
Lastname
Adrress
etc
Cheers
Paul
Something like this?
declare @strBody varchar(1000)
set @strBody=’Database Mail works on ‘+@@servername+’.’
set @strBody=@strBody+ ‘The time is ‘+cast(getdate() as varchar(20))
set @strBody=@strBody+ ”
set @strBody=@strBody+ ”
set @strBody=@strBody+ ‘date’
set @strBody=@strBody+ ‘total 1’
set @strBody=@strBody+ ‘total 2’
set @strBody=@strBody+ ”
set @strBody=@strBody+ ”
set @strBody=@strBody+ ”
set @strBody=@strBody+ ”
set @strBody=@strBody+ ‘name’
set @strBody=@strBody+ ‘lastname’
set @strBody=@strBody+ ‘address’
set @strBody=@strBody+ ”
set @strBody=@strBody+ ”
exec msdb.dbo.sp_send_dbmail
@profile_name=’servername-db email’ /* enter your database profile */
,@recipients=’to_recipient@organization.com’
,@copy_recipients=’cc_recipient@organization.com’
,@from_address=’from_sender@organization.com’
,@body_format=’html’ /* text or html, default is text */
,@subject=’Two tables’
,@body=@strBody
Hi!
Any one knows how to add two HTML tables on the same Sql Email?
Im using this code?
N’ Identifier’ +
N’ Short Title Name’+
N’ Title Name’+
N’ PrintISBN’+
N’ EAN Part-ISBN’+
N’ UPC’+
N’ Name’+
N’ Midle Name’+
N’ IST’+
N’ BD’+
N’LTS’+
Hi Pinal,
I am trying to send database mail. But My requirement is that when ever i sent mail my DisplayUser shoul change. Is it possible?
Please help.
Thanks.
Regards
Abhishek
Hi dave
I got the following error message for several times.
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2010-12-29T19:01:14). 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. 8sm13242854iba.16). )”
how to resolve this error
could you kindly help me i am new to sql database mail.
To solve the following error:
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2008-12-01T07:01:53).
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.5.1 Authentication Required.”
Follow the steps described above, go to the sixth Figure and make sure that the option “This server requires a secure connection (SSL)” is checked.
Very helpful article. Thanks a lot
How to the using Database
Hi all,
I configured the database mail.But i have an issue in Result atachment.The below query is working.
But the datas in the Result file, which i getting in mail is not properly organised and its scrated.What i need to get the mail attachment file in correct format as look likes in sql query result??
Any help would be highly appreciated.Thanks in advance..
EXEC msdb.dbo.sp_send_dbmail
@recipients=’sg@domainname.com’,
@body=’Message Body’,
@subject =’Message Subject’,
@profile_name =’test’,
@query =’select top 10 * from [databasename].dbo.cst ‘,
@attach_query_result_as_file = 1,
@query_attachment_filename =’Results.csv’
I can send a TEXT email but not an HTML email. I have no idea why, my incredibly simple code works with TEXT body format but not HTML…
DECLARE @SUBJECT VARCHAR(250), @BODY VARCHAR(500)
SET @SUBJECT = ‘Offer Choosen for: ‘ + @ADDRESS
SET @BODY = ‘ Click here to view offers:’
EXEC msdb.dbo.sp_send_dbmail @profile_name=’Kazork Admin Email’,
@recipients=’briano@kazork.com,
@body_format = ‘HTML’,
@subject= @SUBJECT,
@body= @BODY