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 Sir,
Iam using SQL server 2005 ,but i am not able to configure Database Mail services on it .main problem is that iam not able to configue SMTP server name and its port number ,
server name , and recipent name .
Please help !
Thanks
Hi, First Go to your SQL Server 2005 Surface area Cofigaration and go to Surface Area Configuration for Features and Enable ur SQl Mail . Iam tried Just 2 Days back Its Working From Sql Sever 2005 . Cheak it out this link for Clear
Firstly thank you for such an enlightening tutorial.
When I first installed SQL 2008, expanding the Management node showed no sign of any email configuration link – finally I removed all trace of my old SQL 2005, SQL 2008 and Visual Studio installations, cleaned the registers, defraged and cleaned the drive.
After doing a fresh install of my new SQL 2008 package all was revealed…it was really worth the effort!
Great work, cheers
Mike dJ
Thankz Sandeep & Imran Mohammed !!!!!!!!
Both of ur posts helped me !!!!!!
I tried this in SQL SERVER 2008 & Works well !!!!!
hi
i want to sent mail in bulk to my clients or user
plz. help me what i can do this with mysql or sql
Hello Vipan,
In SQL Server 2005/2008 configure the database mail and then use sp_send_dbmail stored procedure in loop to fire mails.
Regards,
Pinal Dave
Seems to be a great article bcos so many comments were left.
But I am a newbie and can’t see any images at all to put my solution together.
Please can you look into this. It would be great help.
Thanks
Shri.
Hello Friends,
I am using asp.net, .NET 3.5, C#, and SQL Server Express 2005.
I have created a stored procedure in SQL, and when I run SP from SQL server it takes less than 1 second to return results. I have also tried that query in query analyzer and it also gives me results in less than 1 second. But when I try to call this SP from .NET (C#), it takes a long time, and then gives a timeout error.
//*******************************1. ArtifactEntityDetails*************************************
SqlCommand cmd_delete = new SqlCommand(@”update ArtifactEntityDetails set OriginPlaceId=null where OriginPlaceId=@id” , con);
cmd_delete.Parameters.AddWithValue(“@id” , vCity.ENTITYID);
cmd_delete.Transaction = Tran;
cmd_delete.ExecuteNonQuery();
cmd_delete.CommandText = @”update ArtifactEntityDetails set LocationID=null where LocationID=@id”;
cmd_delete.ExecuteNonQuery();
//******************************* 2.AwardEntityDetail *************************************
cmd_delete.CommandText = @”update AwardEntityDetail set LocationPlaceId=null where LocationPlaceId=@id”;
cmd_delete.ExecuteNonQuery();
Hi Pinal,
Excellent post, I configured my Database Mail using your blog, but how do I ask the stored procedure sp_send_db_mail to send all the file names in the back job when the backup is successful
or fails. I have created a job and sends test mail until the following: but when I add @file_attachements = ‘C:\Program Files\Microsoft SQL Server\….., it states access
denied.
Use msdb
GO
EXEC sp_send_dbmail @profile_name =’DB Mail Profile’,
@recipients =’csaha@imsa.edu’,
@copy_recipients =’csaha@imsa.edu’,
@blind_copy_recipients=’csaha@imsa.edu’,
@body =’This is a test message’,
@subject=’Database Backup’,
@body_format=’TEXT’,
@importance=’Normal’,
@sensitivity=’Normal’
Regards
Chitra
Hi Pinal,
I learn to send mails by using ur article so thx from me .
I want ti fire a mail to multiple peoples which should contain report about daily transaction.
So please guide me fot that.
Hi,
The articals are good in this blog. How to become member and how can post queries
Worked really well, thanks!
I think step 2 may be redundant though as the database wizard automatically configures Database Mail XPs to 1.
The reason I think this is that when I ran the code in step 2 after doing step 1 in both SQL server 2005 and SQL server 2008, the message I got was
‘Configuration option ‘Database Mail XPs’ changed from 1 to 1.’,
which would imply that this setting had already been taken care of by step 1.
Hi,
I read your article. It is perfect. I want to say you thanks, you spent so much time for such valuable article. I implemented this in my project and it is running perfectly.
Whenever i use database mail to send mails,its showing as ‘Mail is queued’ however the mail doesn’t get delivered to the specified destination.
Tried all the above solutions but still not working :(
Thanks for the tutorial. Everything works for me as expected with one exception: it can’t find the stored procedure ‘sp_send_dbmail’. I’m using SQL 2008. Any suggestions?
Dear Sir,
I have a email report jobs that run good, but just some days (random) the email no body that means, looks like @body = @tableHTML no work. However, it really has data in database and if manully run it and had body in email.
Thank you,
Frank
Hello,
PLEASE HELP ME its URGENT……
I have developed one web application through whivh i am able to send emails and its working fine when i run application but when i upload the same application on live server, emails are not sent and i get no error massege…..
I dont understand what is the problem???
Can anyone help me???
Excellend article, all worked fine … but when I set up new maintenance plan, and get to the final page, try clicking on ’email report to’ and it won’t let me, saying “there are no operators defined on the system”. Argh! Hope you can help!
Cheers, Jules
Hi all,
how do we fix the error ‘Relaying not allowed”.
emails work once in a while .. most of the time we see this error in the log
any help is appreciated
priya
hi all,
this is the error
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2010-03-01T13:39:59). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: Relaying not allowed).
how do I fix it?.. we are using third party mail server.
we get this error most of days.. some days it works
priya
I am learning SQL Server 2008 and have setup and configured database mail using smtp. It successfully test sends and email when through there. I also have a maintenance plan but when I get to the page for reporting the check box for send report to an email receipient is greyed and can’t select it? Is something not running that should be?
Thanks
I’m working in SQL Server 2005 and I have a three scheduled jobs that run each night, and on success I’ve specified to be notified by e-mail through DataBaseMail. The e-mail gets properly queued but is not sent unless I manually invoke DatabaseMail90.exe
This has been bugging me for months and I’ve yet to be able to resolve it.
After each scheduled job runs, there is a Database Mail Log entry which states ‘Activation Failure’.
SQL Server 2005 is running on a SBS2003 box, by the way.
Any thoughts on how I should go about troubleshooting this would be appreciated.
Thanks,
Todd