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
This one is really helpful. Thanks a lot for providing information with images, it realy helped..
Thanks for this very informative tutorial. Saved me from potential headaches. Keep up the good work! ^_^
hello everyone,
i want to create a web site in asp.net along with email functionality, i have a SMTP and POP3 server set up over the windows server 2003.
i want an inbox, outbox in my web site for every user who is registered, can i use sql server 2008 maildb to do so.
please guide me to complete my web site….
thanx in advance….
Hello,
Nice article, but i dont know why i am getting this error, i need help. I already did exactly what this article said.
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2011-02-28T15:01:46). Exception Message: Could not connect to mail server. (A non-recoverable error occurred during a database lookup). )
Version -Sql server 2005
Initially we configured Database mail and its worked perfectly but after some months the mail is worked only within domain. Database mail is not sending l outside to the domain say (GMAIL,Yahoo&Hotmail)
Can you please give the solution
Hi every one!
It’s very useful using Database Mail.
I need to write script for automated sending email from SQL Server. I Configure Database mail already.
I have a Database Named AppDB contains a Table named tbl_Material with these columns: MaterialCode (NVARCHAR(20)), MaterialType (INT).
I need to write a store procedure or a function to check IF MaterialType=1 THEN Automatic send email to a recipients.
But the msdb.dbo.sp_send_dbmail can’t be executed in a function. How to fix this issue? Please help me!
Many thanks
Create a stored procedre and schedule it as a job
Pinal,
As always…excellent and most useful article. Simple and too the point. Worked like a champ and saved me a ton of time (now to figure out how to send HTML with images, etc.).
I oh so very often in my searches end up finding help from something you put together. Know there are many people…including myself that are forever grateful for the information you share and the details you provide when doing so.
MANY THANKS! …and please continue the great work!
It was very useful article.
Just I have a question.By sending email we add some rows in sysmail_mailitems, sysmail_log,… and this increases the size of msdb database. Will this cause any problem in long term and also is there any way we can configure Database mail to automatically clean or archive old data in mail tables in msdb database.
Take a look at these two SPs: msdb.dbo.sysmail_delete_mailitems_sp
msdb.dbo.sysmail_delete_log_sp
It looks like you can only pass dates, instead of something like “delete all email older than 30 days old.” Perhaps you could schedule them as a daily or weekly SQL job.
Hi Pinal,
It is a nice article, thanks.
Can you help me in my below problem.
I have set up the profile and account for the mail set up.
It is succesfully tested.
Now the thing is that I do not want to send the mail to the user, but wants to store it in system tables so that I can verify the mail ids in it.
I have tried with below efforts-
1. Use – sysmail_stop_sp to stop the mails, but it will give error while executing – sp_send_dbmail.
Is there any way to do it?
Hi Pinal,
It is a nice article, thanks.
Can you help me in my below problem.
I have set up sql sever 2008 r2 in my computer box running window xp. I did everything as you suggested in the article. But I can not send email. SMTPserver rejected email.
Is there any way to resolve it?
Jun
Navigate to SSMS / Your server / Management folder / right-click Database Mail / Send test email / your email address in the “to” field / Send Test Email button.
What error message do you get? Try checking your spam folder, too.
If we are using configuration wizard, whe have not to use sp_configure
Could somebody help me in following issure:
I can sent mail through sp_send_dbmail. But when I try to send file as attachment with size over 8 MB the MS SQL 2008 gives the following error:
Msg 22051, Level 16, State 1, Line 0
Memory allocation failure.
PS: I’ve set max attachment size to 20 MB.
is server name arbitrary or is the server where smtp is set up?
i did every thing i get this error when selected to send report of profile in email.
The connection to the server has failed. Account: ‘pop3’, Server: ‘mail.optonline.net’, Protocol: SMTP, Port: 25, Secure(SSL): No, Socket Error: 10060, Error Number: 0x800CCC0E
It’s the server where SMTP is setup.
Its really very very helpful.Thank you very much.
Where is the Stored procedure located? under which data base? I have sucessfully completed the setup of wizard, but stuck in the Stored procedure section. Please guide where to place the code in SP and where the SP is located.
It will be stored under the database in which it is created.
Thanks Pinal!
hi sir,
ur given gr8 information about how to send mail through SQL…i am learner
i use it, i learn a lot from this ……
but
1.at the end i didnt undersatnd which server i have to select at the time of creating profile…
i select it http://www.gmail.com
that my account server
2.after at connenction is successfully done
but at the end i didnt got the massage that i have send to my mail id……
hi pinal,
this is praveen madupu here. suppose i’ve created an operator in sql sever and configured a profile using database mail. then how can i check my inbox if i recieve any alert regarding job success or failure status? please let me know the answer.
Thank you very much for sharing this information, really helped me
Hi everybody,
everything works fine when I execute the SP on the Script Editor and hit Execute. My problem arrives when I try to execute the SP
USE [msdb]
GO
EXEC
sp_send_dbmail
@profile_name = ‘MailUser1’,
@recipients = ‘user@domain.pt,
as a T-SQL Task inside a Maintenance Plan.
The View History shows no error, yet the mail never arrives.
Thanks for your help.
Best.
Hello Pinal, Great article :)
Our business requires sending mails to thousands of recieptients continously.
But server is not able to deliver mail(having thousand recipients)…immediately after delivering 2 mails(thousand recipients each).Is this related software or hardware or network related issue ? How can we improve the capacity/performance of the mailing server. We have tried some config setting but found no luck.
Please give us some insight.:)