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

- 2015 Chevrolet Camaro Z28 - The Legend Returns
- Wagon Audi Allroad Vs. Subaru Outback
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.

Top Search -----------------
- Windows Server 2008 R2 : Work with RAID Volumes - Understand RAID Levels & Implement RAID
- Windows Server 2008 R2 Administration : Managing Printers with the Print Management Console
- Configuring Email Settings in Windows Small Business Server 2011
- Windows Server 2008 R2 : Configuring Folder Security, Access, and Replication - Implement Permissions
- Monitoring Exchange Server 2010 : Monitoring Mail Flow
- Windows Server 2008 R2 :Task Scheduler
- Windows Server 2008 R2 : File Server Resource Manager
- Windows Server 2008 R2 : Installing DFS
- Exchange Server 2010 : Managing Anti-Spam and Antivirus Countermeasures
- Windows Server 2008 R2 : Configuring Folder Security, Access, and Replication - Share Folders
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
 
 
Most view of day
- Windows Server 2008 Server Core : Compressing Data with the Compact Utility
- Manage the Active Directory Domain Services Schema : Remove Attributes from the Index
- Add an InfoPath Form Web Part to a SharePoint Web Part Page
- Microsoft Systems Management Server 2003 : Defining Parent-Child Relationships (part 2) - Installing the Secondary Site Locally from the SMS CD
- Windows Server 2003 : Analyzing Traffic Using Network Monitor (part 1)
- BizTalk 2009 : Host Integration Server 2009 - Planning Your Host Integration Server Topology
- Using Windows Live Programs (part 2) - Using Windows Live Mail
Top 10
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 8) - Administering a Failover Cluster
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 7) - Create shared folder on cluster, Testing Failover of Cluster
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 6) - Add primary storage to cluster, Configure service or application
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 5) - Creating a new Failover Cluster
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 4) - Verifying cluster configuration using the Cluster Validation Wizard
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 3) - Connecting cluster nodes to shared storage
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 2) - Adding Failover Clustering feature
- Windows Server 2008 R2 high-availability and recovery features : Installing and Administering Failover Clustering (part 1) - Failover Clustering prerequisites
- Working in the Background : WORKING WITH THE NETWORK LIST MANAGER
- Working in the Background : IMPLEMENTING APPLICATION RESTART AND RECOVERY
Windows XP
Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
Cars Review