KishoR NaiK: MS-SQL - Send Mail from SQL Server 2008

In this article i will show you how to configure and send mail through SQL Server 2008.

Database Mail was introduced in SQL Server 2005 and we can say it is a complete replacement of SQL Mail of SQL Server earlier version. Database Mail is designed for reliability, scalability, security, and supportability.

Advantages of using Database Mail.

It's easy to configure, fast and reliable.
Auditing and Maintaining Logs of every mail.
Can send HTML messages, attachments,Attach Query ResultSet as TextFile.

Let See How to configure Database mail in SQL server 2008.

Step 1

We need to create a profile and account using the Configure Database Mail Wizard.This wizard is used to manage accounts, profiles, and Database Mail global settings.

Open Management Nod, right click on Database Mail and click Configure Database Mail.it is look like this

Click on Image for better View

Step 2

Database Mail Wizard will Open,it is look like this

Click on Image for better View

Step 3

Setup Database Mail,Click Next Button,it is look like this

Click on Image for better View

Step 4

Add profile name and profile description and click Add button,it is look like this

Click on Image for better View

Step 5

Enter Database Mail Account details and click OK button,it is look like this

Click on Image for better View

All the information needed for SQL Server to send an email to a Simple Mail Transfer Protocol (SMTP) server.

Account Name: Name that will be used to identify the database mail account.

Description: Optional description used to identify the database mail account.

E-mail Address: Email address that the mail is being sent from.

Display Name: Optional name that is displayed on email messages to indicate who the email is from.

Reply E-mail: Optional email address that will be used when someone replies to an email sent by this account.

Server Name: Server name or IP address of the SMTP server that is used to send emails for this account.

Port Number: Port number that is used to connect to the SMTP server.

This Server Requires a Secure Connection (SSL): This option will encrypt communication between SQL Server and the SMTP server. You must have a certificate installed for SQL Server in order to use this option.

Windows Authentication Using Database Engine Service Credentials: This option will use the MSSQLServer service credentials to connect to the SMTP server.

Basic Authentication: This option allows you to specify a username and password in order to connect to the SMTP server.

Anonymous Authentication: This option connects to SMTP servers without passing any login credentials and is used for SMTP servers that do not require authentication.

Step 6

Now New Account Profile Create,Click Next Button,it is look like this

Click on Image for better View

Step 7

Manage Profile Security, Click Next Button

Click on Image for better View

We can configure Database Mail profiles as public profiles or private profiles. If you configure the profile as public, anyone who can access the msdb database is allowed to use the profile to send mail. If you configure the profile as private, the profile will be limited to only specific users. You can also have one default public profile and one default private profile. A default profile allows you to send mail without specifying a profile name. When an account has access to both a default private profile and a default public profile, the default private profile will be used. If you would like to make the profile public, select the Public check box next to the profile name; if you would like to make the profile private, select the Private Profiles tab

Note: No need to select any CheckBox just Click Next button.

Step 8

Configure System Parameter,Click Next Button,it is look like this

Click on Image for better View

We can use the Configure System Parameters screen to define the system parameters for an entire instance of SQL Server; any changes you make here will apply to all Database Mail profiles and accounts.

Account Retry Attempts: Number of times Database Mail will retry to send mail using each account in a profile. For example, if you set the Account Retry Attempts parameter to three and you have two accounts in a profile, each account will retry to send mail three times.

Account Retry Delay (Seconds): Number of seconds Database Mail will wait between retries. This delay is not the delay between each account in a profile. Database Mail will attempt to use all accounts in a profile, and then wait the defined number of seconds before trying all accounts again.

Maximum File Size (Bytes): Limits the size of an attachment.

Prohibited Attachment File Extensions: Comma-delimited list of file extensions that are not permitted as attachments when sending Database Mail.

Database Mail Executable Minimum Lifetime (Seconds): The minimum time the external mail process will remain active even if there are no items in the mail queue. If items are in the queue, the external mail process will remain active until all items are processed.

Logging Level: There are three logging levels you can use to send events to the Database Mail log: Normal, Extended, and Verbose. The Normal logging level will log errors. The Extended logging level will log errors, warnings, and informational messages. (Extended is the default logging level.) The Verbose logging level will log errors, warnings, informational messages, success messages, and internal messages. You should generally only use the Verbose logging level for troubleshooting purposes.

Step 9

Complete the Wizard,Click Finish Button,it is look like this

Click on Image for better View

Click on Close Button.

Step 10

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

USE msdb
GO
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

Step 11
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.

1. Send Basic Email

-- Send Basic Email
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Test message from Sql Server',
@body='Sending mail from SQL Server 2008'

Output

Click on Image for better View

2. Send Select Query ResultSet

-- Send Select Query ResultSet
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Query Result Set',
@body='Select Query Result Set',
@query='SELECT Northwind..Employees.FirstName,Northwind..Employees.LastName FROM Northwind..Employees
'

Output

Click on Image for better View

3. Send mail attach the Select Query Resultset as Text File

-- Send mail attach the Select Query Resultset as Text File.
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Attach the Result as Text File',
@body='Find the attachment.',
@query='SELECT Northwind..Employees.FirstName,Northwind..Employees.LastName FROM Northwind..Employees
',
@attach_query_result_as_file = 1,
@query_attachment_filename ='EmployeeResults.txt'

Output

Click on Image for better View

4. Send mail with Attachment

-- Send mail with Attachment
EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='Send Mail with Attachment',
@body='Find the attachment.',
@file_attachments ='D:\Koala.jpg'

Output

Click on Image for better View

5. Send mail that uses HTML Formatting

--Send mail that uses HTML Formatting
--Send mail that uses HTML Formatting
DECLARE @HtmlBody Nvarchar(MAX)

 SET @HtmlBody='<table border=1 cellpadding="5" cellspacing="5">'
 SET @HtmlBody=@HtmlBody+'<tr>'
 SET @HtmlBody=@HtmlBody+'<th>First Name</th>'
 SET @HtmlBody=@HtmlBody+'<th>Last Name</th>'
 SET @HtmlBody=@HtmlBody+'</tr>'
 
 SELECT @HtmlBody=@HtmlBody+'<tr><td>'+Northwind..Employees.FirstName+
     '</td>'+'<td>'+Northwind..Employees.LastName+'</td></tr>' 
     FROM Northwind..Employees
     
 SET @HtmlBody=@HtmlBody+'</table>'

EXEC sp_send_dbmail @profile_name='KishorNaik',
@recipients='kishor.naik011.net@gmail.com',
@subject='HTML Formatting',
@body=@HtmlBody,
@body_format = 'HTML'

Output