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
Help me plz, I am able to write a SP to send mail from mssql 2008, which worked fine when i ececute it from databases, but when I try to execute the same SP from my asp pages it doesnt works.
IS it that I cannot call SP of master database with the connection or something else.
And the more whenever I call other SP which doesnt sends mail , They works fine.
it should be work actually.
but if its not working, why don’t you try to send an email from asp.net side ?
Yash
Pinal,
I am sending mails from sp through database mail for the past few months & every thing was working fine. But today when I tried to send newsletters to around 12000 members, only 500 mails got to the queue, I tried again and 700 mails got queued. The third time 800 mails got queued. I am not sure why this is happening. I have only one profile and one email id associated with it. There are other sps using database mail. Will the mail get cut off when another sp starts queuing up messages. In between I tried to get the count of mails queued. Will this stop the queue?
Please let me know where I am going wrong.
Thanks in advance.
Regards,
Shankar
hello sir,
i am using sql express 2008, this express is not send a mail, only stored in queue, how to send mail in sql erpress 2008?
Hi,
I want to get database alerts on my mail every day? How can i get it and where to configure it in sql server 2008. Please do the needful to me?
Hi,
You can create a SP as per your requirement and using this MSDB.DBO.SP_SEND_DBMAIL stored procedure , you can send an email.
Let me know if you need more help in this.
Thanks,
Yash
Hi i created a database in sql server 2008 and now i want to create a front end for that through which i can sell the products in my database and should be able to generate a form and a response can we do it in sql server 2008?
if we can how can we create it?
please help me on this one.
In Database Mail How many emails, we can sent in a day… using gmail account….
if any limitation then how can we send 100000 emails per day through database mail and from which mail account..????
anyone help me out ???
Hi Pinal,
i’m using SP_Send_DBMail sp to send an email notification from SQL SP.
I can get the parameters @RECIPIENTS, @COPY_RECIPIENTS where i can sent the email id dynamically to whole i want to send an email notification.
I’m using my own profile in which i have mentioned id “DoNotReply@msfd.com”
so whevever any email will be sent from this SP, it will be sent from “DoNotReply@msfd.com”.
Now my question is, i want to dynamically set email id in From part.
i want to use dynamically id instead of “DoNotReply@msfd.com” id from my own profile.
How can i implement this?
Thanks in advace,
Yash Thakkar
QUESTION QUESTION QUESTION:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N’JDOE’
,@body=’Message Body’,
@subject =’Message Subject’
— “JDOE” is the NT Profile name
the email shows ‘queued’ but it doesn’t get delivered, anyone had to use “DOMAIN\USER” as @recipients or just “NT USER ID” as the @recipients?
Please respond if you know it works or not.
I am positive, it used to work with SQL Mail (SQL 2000).
Dear Sir,
Actual i want send to my query result..So how can i config that settings
please help me sir.
Hi, I am getting the following error after following each and every step. help required.
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 4 (2010-08-06T21:53:29). Exception Message: Could not connect to mail
server. (A connection attempt failed because the connected party did not properly respond after
a period of time, or established connection failed because connected host has failed to respond
203.170.87.177:25). )
Hi Hassan,
Check whether you are connected to Internet while sending mails..you must have connected while sending database mails.
Hi,
Can we use the SQL database mail for SQL Server 2008 R2 Web Edition ?
Thanks for the article
as i’m new in SQL Server i dont see Database mail nod in managment nod?and i do’nt know why?
regards
This works perfectly. thank you so much. but i will make this much simple.
01.use your mail server.(if you work, then company mail server)
To find the mail server go to sql reporting services configutration manager and click mail settings.
02.keep port as 24.
03. uncheck the Require secure connections checked
04. under “Basic authentication”
username: give a email address that use as sender(from:) of the mail.
password: type the email address password you provide above.
The above configuration is for Database mail.
hey hi friends,
how to configure sql server 2008 to sms on mobile
when job fails or when new recored inserted(using triggers) please reply me.
sql server 2008 to SMS on mobile its working or that feature is their??
The database mail was failing for me until I checked the Annonimious authentication in the user setup. Then it worked like a charm. Thanks for the post.
John.
hi pinal,
i am not getting the query output
Substation_ID ————— (0 rows affected)
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘LvrtAlert’,
@recipients = @varRecipients,
@copy_recipients = @varCCRecipients,
@body_format = ‘HTML’,
@subject = ‘LVRT Alert’,
@body = @varDateTime,
@query=’Select distinct Substation_ID From lvrt’,
@attach_query_result_as_file=0,
@query_attachment_filename=’Substation.txt’,
@append_query_error=1,
@exclude_query_output=0
Hi,
I am using MSSQL 2008 R2 version and generating a daily report by the scheduler. I need this report to send by an email daily. Is there any method where MSSQL server sends directly to Mail box?
Can anyone help me.
hello i need to use alternate table row colors. can any one help me. am new in sending mails through sql server DB.
Hi Pinal,
Thanks for this beautiful article..I can send mail now…but please again do me a favor….I have a main server say X and another server say Y(That is My System).I am connected to the main server with administrative login from my System(Y)…and configure database mail in mail server..but not able to send mail.How can i send mail from other servers through a single system(Y here)??
Its working for me
Brilliant Information from Imran.I am using smtp.gmail.com as server name.
THANKS !!!!!!!!