Logo
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
EPL Standings
 
 
Windows Server

SQL Server 2008 : Database Mail (part 2) - Configuring, Sending, Cleanup Procedures Database Mail Using T-SQL

6/13/2011 5:56:01 PM

2. Configuring Database Mail Using T-SQL

I personally prefer using a T-SQL script to set up Database Mail. Not only is it faster when setting up a new server, you can also be sure that you have the same Database Mail configuration on all of your servers. The first thing you need to do is make sure you have Database Mail enabled. You can do this by using the sp_configure stored procedure as follows:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

You also need to make sure Service Broker is enabled for the msdb. Database Mail depends on Service Broker to deliver email messages. If Service Broker is not enabled, your mail messages will queue, but they will not be delivered. You can run the following query to determine if Service Broker is enabled.

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

If Service Broker is disabled, you can enable it by running the following ALTER DATABASE command. Enabling Service Broker requires a database lock. You will need to stop SQL Server Agent before running the ALTER DATABASE command so that Service Broker can acquire the appropriate lock.

ALTER DATABASE msdb SET ENABLE_BROKER

Now you need to add a Database Mail profile. You can do this using the sysmail_add_ profile_sp stored procedure. The following code adds a profile using the same information given in the Database Mail Configuration Wizard.

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Mail Profile',
@description = 'Profile used by the database administrator to send email.'

You can use the sysmail_add_account_sp to create the mail accounts. The following code will create a mail account using the same information given in the Database Mail Configuration Wizard.

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA Mail Account',
@description = 'Profile used by the database administrator to send email.',
@email_address = 'DBA@somecompany.com',
@display_name = 'KEN-PC\SQL2K8',
@mailserver_name = 'KEN-PC'

Once you have created a profile and an account, you need to associate the account with the profile by using the sysmail_add_profileaccount_sp stored procedure. The following code binds the DBA Mail Account to the DBA Mail Profile with a priority (sequence number) of 1. If you add multiple accounts with the same priority, Database Mail will randomly choose the account that sends the mail.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA Mail Profile',
@account_name = 'DBA Mail Account',
@sequence_number = 1

The final script is shown in Listing 1. You can change the script to fit your organization by adding multiple accounts or changing parameters to the correct values. By using the @@ServerName function in the display name, each server will be able to send email using its own name. As you can see, creating a Database Mail script is a far more efficient way to set up Database Mail across multiple servers

Example 1. Database Mail Setup Script
--MAKE SURE TO STOP SQL SERVER AGENT BEFORE RUNNING THIS SCRIPT!
USE msdb
GO

--Enable Database Mail
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

--Enable Service Broker
ALTER DATABASE msdb SET ENABLE_BROKER

--Add the profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DBA Mail Profile',
@description = 'Profile used by the database administrator to send email.'

--Add the account
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'DBA Mail Account',
@description = 'Profile used by the database administrator to send email.',
@email_address = 'DBA@somecompany.com',
@display_name = (Select @@ServerName),
@mailserver_name = 'KEN-PC'


--Associate the account with the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DBA Mail Profile',
@account_name = 'DBA Mail Account',
@sequence_number = 1

Print 'Don't Forget To Restart SQL Server Agent!'

3. Sending Database Mail

Now that you have configured Database Mail, you are ready to start sending email. You can send mail by using the sp_send_dbmail stored procedure, but you must be a member of the DatabaseMailUser role in the msdb in order to execute it. You can use the sp_send_dbmail stored procedure to send everything from a basic email message to email messages that contain file attachments and query results that are formatted using HTML. Now you can schedule a job to send out that weekly report you have been running manually. Let's look at a few examples.

The following statement will send a basic email with a subject and a body. You can add multiple recipients by using a semicolon as a separator. If the statement is executed successfully, you will see the output "Mail queued" in the Messages pane.

--Basic email
EXEC msdb.dbo.sp_send_dbmail
@recipients='Somebody@SomeCompany.com', --[ ; ...n ]
@subject = 'Basic Database Mail Sample',
@body= 'This is a test email.',
@profile_name = 'DBA Email Profile'

All you have to do to attach a file is use the same basic syntax previously listed and add the @file_attachements parameter along with the file location. To add multiple file attachments, you can separate each file using a semicolon, just as you can with multiple recipients.

--Code to send an email attachment
EXEC msdb.dbo.sp_send_dbmail
@recipients='Somebody@SomeCompany.com', --[ ; ...n ]
@subject ='Database Mail Sample With File Attached',
@body='This is a test email.',
@profile_name ='DBMailProfile',
@file_attachments ='C:\SomeFile.txt'; --[ ; ...n ]

You can use the following sample to send an email with an attachment that contains all the databases on the server. Be sure to specify the database that the query in the @query parameter should be executed against by using the @execute_query_database parameter, or you could experience unexpected results or errors by executing the query against the wrong database. If you leave off the @attach_query_result_as_file and @query_attachment_filename parameters, the query results will be displayed in the email following the body text.

--Code to send query results
EXEC msdb.dbo.sp_send_dbmail
@recipients='Somebody@SomeCompany.com', --[ ; ...n ]

@subject ='Query Results As File Sample',
@body='This is a test email.',
@profile_name ='DBA Email Profile',
@query ='SELECT Name FROM sys.sysdatabases',
@execute_query_database = 'master',
@attach_query_result_as_file = 1,
@query_attachment_filename ='Databases.txt'

You can also send an email that uses HTML formatting. All you have to do is take advantage of the For XML Path clause to form the body of the email and use some basic HTML tags. By giving the name column an alias of 'td', each name in the result set will be wrapped in <td> tags, which represent a table cell in HTML. The 'tr'For XML Path clause will wrap each row in <tr> tags, which represent a table row in HTML. The result set is then given a header using the <th> tags and wrapped in a <table> tag to form a perfectly formatted HTML table using query results. following the

--Code to send an HTML email message
DECLARE @HTML NVARCHAR(MAX) ;

SET @HTML =
'<table border="1">' +
'<tr><th>Name</th></tr>' +
Cast((SELECT name as "td"
FROM master.sys.sysdatabases
FOR XML PATH('tr')) as NVARCHAR(MAX)) +
'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@recipients='Somebody@SomeCompany.com', --[ ; ...n ]
@subject ='HTML Sample',
@body= @HTML,
@body_format = 'HTML' ,
@profile_name ='DBA Email Profile'

Once you have sent an email, you can use the sysmail_allitems view to see all the items that have been processed using Database Mail, as shown in the following query. There are a few other views that may be useful as well. For example, the sysmail_faileditems view shows only failed messages, sysmail_usentitems shows only unsent messages, and sysmail_sentitems shows only sent messages.

SELECT * FROM msdb.dbo.sysmail_allitems

4. Database Mail Cleanup Procedures

Database Mail keeps a copy of every email that is sent along with several event log entries in the msdb. Over time, this buildup of sent mail can cause excessive growth in the msdb database. There are two stored procedures you can use to purge mail history in order to keep the msdb database at a manageable size.

You can use the sysmail_delete_mailitems_sp stored procedure to delete email messages older than a specified date or all emails with a certain status. If you execute the sysmail_ delete_mailitems_sp stored procedure without supplying any parameters, all email messages will be deleted. Following is the complete syntax:

sysmail_delete_mailitems_sp
[ [ @sent_before = ] 'sent_before' ] -- '1/1/2009'
[ , [ @sent_status = ] 'sent_status' ] -- sent, unsent, retrying, failed

You can use the sysmail_delete_log_sp stored procedure to delete Database Mail logs older than a specified date or all Database Mail logs for a certain event type. If you execute the sysmail_delete_log_sp stored procedure without supplying any parameters, all Database Mail log entries will be deleted. The complete syntax is as follows:

sysmail_delete_log_sp
[ [ @logged_before = ] 'logged_before' ] --'1/1/2009'
[, [ @event_type = ] 'event_type' ] --success, warning, error, informational


You should incorporate both of these cleanup stored procedures into your regular maintenance routines.Come up with an acceptable retention policy for Database Mail, and then execute the cleanup stored procedures accordingly.

Other -----------------
- SQL Server 2008 : Monitoring Your Server - Using Server-Side Tracing
- BizTalk 2009 : Host Integration Server 2009 - Security
- Microsoft Dynamics CRM 2011 : Attaching Files to Accounts and Contacts
- Upgrading to SharePoint 2010 : Performing Post-Upgrade Configurations
- Upgrading to SharePoint 2010 : Performing an In-Place Upgrade (part 2) - Configuring the In-Place Upgrade
- Upgrading to SharePoint 2010 : Performing an In-Place Upgrade (part 1) - Installing the In-Place Upgrade
- Upgrading to SharePoint 2010 : Performing a Database Attach Upgrade
- SQL Server 2008 : Monitoring Your Server - Leveraging the SQL Server Profiler
- SQL Server 2008 : Monitoring Your Server - Monitoring Disk IO
- Troubleshooting Exchange Server 2003 Server Migration and Interoperability (part 2) - Using the Netdiag and Dcdiag Command-Line Utilities
- Troubleshooting Exchange Server 2003 Server Migration and Interoperability (part 1)
- Install Windows Server 2008 R2 Roles (part 2) - Install Roles on a Windows Server 2008 R2 Server Core Installation
- Install Windows Server 2008 R2 Roles (part 1) - Install Roles on a Windows Server 2008 R2 Full Server Installation
- Plan for Windows Server 2008 R2 Roles
- Migrate to Windows Server 2008 R2
- BizTalk 2009 : Host Integration Server 2009 - Transaction Integrator
- BizTalk 2009 : Host Integration Server 2009 - SNA Load Balancing
- Microsoft Dynamics CRM 2011 : Creating a Contact
- Microsoft Dynamics CRM 2011 : Creating an Account & Using Parent Accounts and Sub-Accounts
- SharePoint and Restoring a SQL Server 2008 Backup
 
 
Most view of day
- Leveraging the SharePoint Workspace : Leveraging the SharePoint Workspace, View Your List and Display Form Offline
- Preparing Windows PE : Automating Windows PE, Using Windows PE with BDD
- Securing the Workstation : Applying the Castle Defense System (part 5) - Managing information access
- Conducting Research in OneNote 2010 : Handling the Research Task Pane
- Windows Server 2012 : Managing Users with Local Security and Group Policies (part 1) - Viewing Policies with the Group Policy Management Console, Creating New Group Policies
- BizTalk 2006 : Getting Started with Pipeline Development (part 3) - Configuring Recoverable Interchanges, Using the Default Pipelines
- Microsoft Visio 2010 : Working with Data - Creating Reports (part 1) - Introducing the Report Definition Wizard
- Securing the Workstation : Applying the Castle Defense System (part 4) - Hardening the system - USB Device Control, Windows Defender
- Adobe Dreamweaver CS5 : Using Java Applets
- Microsoft SharePoint 2013 : Working with Visio Services - Designing dashboards - Data linking (part 3) - Mapping external data to shapes
Top 10
- Windows Phone 8 : Orientation and the PhoneApplicationPage Class - Setting Page Orientation at Runtime
- Windows Phone 8 : Orientation and the PhoneApplicationPage Class - PhoneApplicationPage Orientation Property
- Using the Windows 7 Libraries : USING THE EXPLORER BROWSER CONTROL (part 2)
- Using the Windows 7 Libraries : USING THE EXPLORER BROWSER CONTROL (part 1) - Adding the Explorer Browser to Your Toolbox , Configuring the Explorer Browser Example
- Using the Windows 7 Libraries : CONSIDERING USER-DEFINED COLLECTIONS
- Using the Windows 7 Libraries : USING NON-FILESYSTEM CONTAINERS
- Using the Windows 7 Libraries : WORKING WITH KNOWN FOLDERS
- Microsoft Exchange Server 2007 : Implementing Client Access and Hub Transport Servers - Installing the Hub Transport Server
- Microsoft Exchange Server 2007 : Implementing Client Access and Hub Transport Servers - Transport Pipeline
- Microsoft Exchange Server 2007 : Hub Transport Server Policy Compliance Features (part 4) - Message Classification , Rights Management and the Hub Transport Server
 
 
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
2015 Camaro