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,
Iam using sql server 2005 express edition.
i have configured all things u said. once execute the mail command show “MAIL QUEUED”.
then i checked sysmail_mailitems table mail status not send. i saw the log file it will show description column – “ACTIVATION FAILURE”.
plz give me the soln asap….
thanks,
mohan.
Hi,
i am using asp.net 3.5
In my web application i am sending mails though asp.net csharp but it always gives me this error
Mailbox unavailable. The server response was: 5.7.1 Client does not have permissions to send as this sender
If u have any idea
can u tell me if i can solve the problem using databse mail
Hi,
SQLSERVER:SQL SERVER 2008 SP1
Firewall:No firewall
Mail server : CCMailServer on windows 2003 server R2
when I send e-mail via sql server, the following error appear :
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2011-05-31T15:42:52). Exception Message: Cannot send mails to mail server. (authentification failure.). )
it work correctly with Microsoft Outlook 2007
EXEC master..xp_smtp_sendmail
@TO = ‘myemail@gmail.com’,
@from = ‘myemail@gmail.com’,
@subject = ‘ failed.’,
@server = ‘smtp.gmail.com’
Hi Pinal Dave,
I was able to create the database mail profile using your tutorial. This worked perfectly. I now need to create a procedure that will loop through a cursor and send an automated email response based off of the criteria. The email needs to be a formatted template so I need to incorporate HTML into the procedure. Is there a way to do this? If so, can you please provide me with some code in how I would do this. Thank you.
Kellylee
Hi Kelly,
Its possible. I’ve done the same thing for 4-5 times. If you are still looking for the solution for the same problem, mail me on [email removed]
Thanks,
Yash
Hi Pinal
Do you know of a way to track any bounced emails through SQL Server? We have an Exchange server if that helps.
Thanks!
-Kevin
Hi Pinal it says mail qued however in the SELECT *
FROM sysmail_mailitems
GO
SELECT *
FROM sysmail_log
GO
teh status is 3
what can i do to push it thru
Hi Pinal and everyone!
i have a question about this issue.i tried various things but i cannot solve it.
Firstly,i am using db mail and i can send e-mail on SQL SERVER . i want to this mail contains embedded image(company logo) not attachment….
im using like this;
@body=’ ….
‘
i sent this e-mail and recipients take it on the various mail platform but ;
1-image does not seem.it seem like this[X]
2.Recipients take it like a spam mail..
Have u got any solition?Thanks a million !
Hi Pinal and everyone!
i have a question about this issue.i tried various things but i cannot solve it.
Firstly,i am using db mail and i can send e-mail on SQL SERVER . i want to this mail contains embedded image(company logo) not attachment….
im using like this;
@body=’ ….
‘
i sent this e-mail and recipients take it on the various mail platform but ;
1-image does not seem.it seem like this[X]
2.Recipients take it like a spam mail..
Have u got any solition?Thanks a million !
Easy to understand and working fine
Nice post!thanks
Great tutorial!
thanks
Do you have a nieuwsgroup where i can discribe to read more of your documents?
Greetings from the Netherlands,
Fokko Dusseljee
hi
actually i have to release the server and move the contents of that server to other so what actually i have to do is there any procedure to do so .
Hi!
I configured the database mail as above mentioned successful.
If try to include the @query in my statement the following Error message appears:
Meldung 22050, Ebene 16, Status 1, Zeile 0
Error formatting query, probably invalid parameters
Meldung 14661, Ebene 16, Status 1, Prozedur sp_send_dbmail, Zeile 504
Fehler beim Ausführen der Abfrage: Meldung ‘208’, Ebene ’16’, Status ‘1’, Server ‘BUNDSTAT’, Zeile 1
‘Ungültiger Objektname ‘AdventureWorks2008R2.Production.WorkOrder’.’
This is my test query statement:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘BUNDSTAT_Mail’,
@recipients = ‘oliver.veit@bund.net’,
@query = ‘SELECT COUNT(*) FROM AdventureWorks2008R2.Production.WorkOrder
WHERE DueDate > ”2006-04-30”
AND DATEDIFF(dd, ”2006-04-30”, DueDate) < 2' ,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;
(Config.:SQL : SQL Server 2008 R2 64bit and Windows 2008 R2)
What did I made wrong?
Greetings from good old Germany
Oli
I tried to configure database mail,i activated mail stored procedures from sql surface area configuration,but still it dont appear option called ‘Configure Database Mail’ when i right click on Object Explorer>Management>Database Mail.
Please Help………..
thanks for this, worked first time
From your tutorial I have managed to make automatic job scheduler
Thank U man
I like ur tutorials
thanks a lot
Hey Pinal,
Can I do IRM settings while sending mail from SQL database. Like I dont want the users to forward, copy, reply that mail.
Thanks
@Pinal
Hi,
I’ve configured DB mail on my local database and worked successfully for my website.
I’ve shifted my web and DB servers on cloud. Now I’m facing problem in sending mail via DB mail. I’ve my SMTP server at different locaton from my DB and WEB servers.I’ve opened the SMTP port on my mail server for sebding DM mail. But still no success.
sometimes I’m getting the following error:
Message
1) Exception Information
===================
Exception Type: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
====================================
Sometimes I’m getting the following error:
Message
1) Exception Information
===================
Exception Type: System.NullReferenceException
Message: Object reference not set to an instance of an object.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.Account GetAccount(Int32)
HelpLink: NULL
Source: DatabaseMailEngine
StackTrace Information
===================
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.SessionManager.GetAccount(Int32 accountID)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandFactory.CreateSendMailCommand(DBSession dbSession)
at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db)
at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
Please suggest the solution.