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
Thanks all,
I can send mail from remote server now.
Now i want to attach file from remote computer…but get Invalid File Attachment error..how can we send attachment from remote computer??
Hi Joe,
Use should use port number 587 or 2525 and smtp.gmail.com as server name if you are using gmail account as sender.Also you must logged in with your account
Thanks Neeraj,
For gmail account, with 587 is not working, but is working fine with 2525 port!
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2012-01-14T13:55:06). Exception Message: Cannot send mails to mail server. (Failure sending mail.)
Getting this even though I’m using identical host/port and credentials in an email client on the same machine that’s able to send without issue.
Hi Pinal,
Thanks for this beautiful article.I succeed to send database mail using query.But i’m facing problem of design of that mail. This mail is not going in proper format.Also tell me can we remove dotted lines below column header and number of row affected below query result.
Hi pinal
can we hide sender mail address when we send mail of we need to change the display name of the sender mail
Getting the following message when trying with Gmail :
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2012-02-20T16:55:44). Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 173.194.66.109:587).
Getting following error massage in sysmail_log table –>The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2012-02-23T17:19:35). Exception Message: Could not connect to mail server. (No such host is known). )
Hi There .. I am trying to configure Database mail on SQLserver 2005 using the standard SMPT port (25) using smpt server out of the company. I have open the port 25 and I have configured outlook express on the server using the same configurations as in the Database mail. I have sent email from outlook successfully and but from database mail it was not working and always give the following erorr in the (View database mail log).
“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 5 (2012-02-26T10:32:22). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 217.66.226.23:25). )”
thansk Alot
Hi,
I am trying to set a solution which will send a email once data get inserted in the table.
The problem is I have attachment also saved in binary format which I want to send with attachment by converting binary to doc file.
I have this in table.
ToEmail, FromEmail and binary data of resume. I want to send a email once any records get inside the table with all the fields.
Please help me I am very new in SQL.
Thanks.
Weird. We just moved our email hosting to a new host last week and their SMTP servers required SSL on ports 995 and 465 respectively. Couldn’t get my Database Mail to work regardless of what I tried. Finally I unchecked the SSL box and set the port to 25. Works like a charm. go figure.
can any one help me i am getting error when sending mail is as:
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.CommandFactory.CreateCommand(DBSession dbSession) at Microsoft.SqlServer.Management.SqlIMail.Server.Controller.CommandRunner.Run(DBSession db) at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.ThreadCallBack.MailOperation(Object o)
It’s a remarkable post and the mail article is very good and helpful.
How to edit database mail configuration, which is already configured?
Error: after sending the email
1) Exception Information =================== Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException Message: Could not retrieve item from the queue. Reason: the list of accounts which could send this mail is empty (probably due to the use of invalid profile). Data: System.Collections.ListDictionaryInternal TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Controller.ICommand CreateSendMailCommand(Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DBSession) HelpLink: NULL Source: DatabaseMailEngine StackTrace Information =================== 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)
I am getting this error.
simple, clear, fast, thank you
Wow amazing…worked out
Thanks alot pinal.
Hi Pinal, Earlier our mail server working on simple smtp 25 and with out ssl. Now we have change our service provider where our incoming server need SSL where our incoming mail server port on 995 and smtp port 587 with TLS authontication. How can we configure TLS from ACL because our Payroll does not support GUI configuration.
Hi,
Thanks for this article
I followed your steps and the now the mail is sending fine also with attachment
As per you said, i created an account with Email Address and Password.
In every time the From Address is treat as the Mentioned Email address in the account
Not Taking the From Address passing in the Parameter
I need to show the From address what i am passing from the Stored Procedure
so how to do?
please give some suggestion, thank you
Hi pinal dave
i am sending email from sql server 2008 r2.
database mail configuration details set as :
————————————————————–
Server Name : smtp.gmail.com
Port No : 587
Set Basic Authentication : User Name, Password
i tried more but email sending is failed and error occur is-:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2012-05-03T15:46:30). Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 173.194.79.108:587). )
so how to do?
please give some suggestion.
Thanks and Regards:
Ganpat Sharma
Hi everyone
i am sending email from sql server 2008 r2.
database mail configuration details set as :
————————————————————–
Server Name : smtp.gmail.com
Port No : 587
Set Basic Authentication : User Name, Password
i tried more but email sending is failed and error occur is-:
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2012-05-03T15:46:30). Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 173.194.79.108:587). )
so how to do?
please give some suggestion.
Thanks and Regards:
Ganpat Sharma
I love reading Pinal Dave. He’s clear, concise, and to the point. 3 cheers for him !