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
when i use the @query option database mail fails to send the email, if i quote it it works just fine.
Is there a way to use it fine?
or just work arounds like generating strings outside the email and later merge them in the @body option (which i dont want to).
note: using SQL2008, i know query works in a separate process, double single quote marks used, query was tested and works fine, @execute_query_database used to relate schema
@Boxek
What is your question ?
Is there a way to use it fine ?
This does not makes any sense to me.
You already know a solution, When you dont want to use it, please specify why you dont want to use a solution and what is that you want us to do.
Please help us to help you by providing complete information.
~ IM.
Sorry, my english might be not good, hehe. I meant, that i followed Pinal’s tutorial and everything worked out just fine, to send mail with database mail.
But, if i use something like
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.’
–this is the problematic part–
@query = ‘SELECT * From sysobjects’;
@attach_query_result_as_file = 1,
@query_attachment_filename =’Results.txt’;
It shows an error (i cant remember the exact sentence, but i’ll write again when im in my workstation). My question is: Why if i use @query option i get an error (the sentence works) and it doesn’t show error if i dont use that option.
Sorry i had some misspellings in the code (in the comment, not in my test source). I’ll write it again:
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.’,
– – this is the problematic part
@query = ‘SELECT * From sysobjects’,
@attach_query_result_as_file = 1,
@query_attachment_filename =’Results.txt’;
Hi Boxek
Try @query = ‘SELECT * From master.dbo.sysobjects’,
Make sure you are setup as DBA on the server.
Thank you for the nice overview.
I would also recommend that you also add some instructions and screenshots showing how to configure SQL Server Agents to use Database Mail.
Microsoft’s article cover it here: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms190437(v=sql.90)
Enjoy.
Thank you very much to every one
I learned a lot today
Hi Pinal
When i Right click on the database mail i am not able to see the option to configure database mail please help me
Hi Imran & Pinal,
I go the Database Mail setup correctly and able to send test mails to my email id.
Now my question is in real time i hava a scenario it works like this.
Every day @10Pm i have to send an email to users.
That email will consists of results for 5 columns from different tables using joins.
I like to know how to do this step by step.
Do i have to create this using trigger, or stored procedure or jobs or alerts.I’m totally confused.
and this is very urgent for me.
Please send me the reply asap.
@srinivas reddy
Read this article,
Read : query result section.
Step 1: Write a sql statement, to send email, put you select statement in the same query and send the output of the query in the email.
Step2: Now put this script in a job as one of the job step and then schedule this job to run at 10 PM.
~ IM.
Thanks Imran..it worked for me.
Thansk a lot.
Hi Pinal,
I want to send email when the SSIS Package fails due to any reason. If you know how achive this task then please let me know asap.
Thanks in advance
Pratik
@Pratik,
Execute SSIS Package as a job, and schedule that job.
And set a notification to that job, like if job fails then send an alert. Meaning when SSIS Packege will fail, job will fail and if job fails then you will get a notification email.
~ IM.
Dear, its very very very helpful, now i can send some automated e-mail through sqlserver.
but now my problem is i want to send the result of the qeury, i don’t want to send plain, please let me know, how can i send the data which will be the result of my Select Statement.
example.
select buyername, address, credilimit from emp
where creditlimit > 25,000.
@RamZan
Read this article,
Read : query result section.
Step 1: Write a sql statement, to send email, put you select statement in the same query and send the output of the query in the email.
~ IM.
Hi all, I have configured database mail and it is working well.
My query is is there any possibility to configure database mail on SQL server 2008 Express Edition.
Thanks
Hey Pinal,
It’s indeed a greate articale.
Good efforts, thanks a ton.
Ninad
MCTS
Hey Pinal,
i have two consecutive statements in the same procedure to send mail . it was working for almost 2years. all of a sudden we are facing a problem. some time only first mail is going through. second mail fails with the reason …..Relaying not allowed”. what went wrong? How do I solve this issue?. any help is appreciated
priya
Hi Pinal,
I am adding these to the above post
I am using Sql server 2005,
I am sending mails using EXEC msdb.dbo.sp_send_dbmail
we don’t have Exchange server. once we configured the database mail, is it using any SMTP sever?. could you clarify how the mail works?
we are using the third party email server, Network solutions for our office mails.
where do I configure the “Relaying”.. We don’t have IIS in the sql server machine
The mail works sometime , but sometimes it fails. Is this a network issue?
i did all the configurations for gmail it did not work…
but works fine for mail company id and server name and with given port number
answers:
priya:
yes, sql it is sending using an SMTP server; this is configured when creating the mail profile along with the accounts; these accounts are “SMTP accounts”, that means these they are used to send the email
katheesh kumar:
mail sending is restricted to local company accounts; in order to send email to outside you have to add the exceptions (external mail addresses) or make an automatic relay to forward the emails form the sql server