SQL SERVER – 2008 – Configure Database Mail – Send Email From SQL Database

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:

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:

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database step-2 openWizard

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database step-4 CreateProfile

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database step-5 CreateProfile2

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database step-6 AddAccount

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database step-7 SMTPAccounts

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database step-8 Manage

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database step-9 Parameters

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database step-10 Complete

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database step-11 Success

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

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database ConfigQuery

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.'

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database SendQuery

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

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database MailLog

Status can be verified using sysmail_sentitems table.

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database sent_status

After sending mail you can check the mail received in your inbox, just as I received as shown below.

SQL SERVER - 2008 - Configure Database Mail - Send Email From SQL Database DBMailSent

Let me know what you think about this tutorial.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case – Part 2
Next Post
SQL SERVER – Fix : Error : 40 – could not open a connection to SQL server – Fix Connection Problems of SQL Server

Related Posts

478 Comments. Leave new

  • Hi,
    SQL Server 2016 is not capturing invalid email id in the sysmail_faileditems.
    If the domain is not valid it is capturing in the faileditems. But if the email address is not valid, it says sent successfully.
    Is there anything to do in settings to capture the invalid email address too
    for example: abc@abc111.com is not captured as failed
    abc@abc111.com? or abc@abccom or abc@abc!com is captured as failed.

    Reply
  • Hi,

    After configure the database mail, when i am trying to send mail using sp_send_dbmail. i am getting below error.
    Can someone please help me to fix this?

    1) Exception Information =================== Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
    Message: The Transaction not longer valid. Data: System.Collections.ListDictionaryInternal
    TargetSite: Void ValidateConnectionAndTransaction() HelpLink: NULL Source: DatabaseMailEngine HResult: -2146232832
    StackTrace Information ===================
    at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.ValidateConnectionAndTransaction()
    at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.ConnectionManager.RollbackTransaction()
    at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.GetDataFromQueue(DataAccessAdapter da, Int32 lifetimeMinimumSec)
    at Microsoft.SqlServer.Management.SqlIMail.IMailProcess.QueueItemProcesser.ProcessQueueItems(String dbName, String dbServerName,
    Int32 lifetimeMinimumSec, LogLevel loggingLevel, Byte[] encryptionKey, Int32 connectionTimeout)

    Reply

Leave a Reply

Menu