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

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.