E-mail

SQL Server has the capability to interact with administrators and users via e-mail. Usually, operators are notified by SQL Server when specific events occur. You can use the Alert and Operator mechanisms to implement and define this behavior.

This feature is an alternative to standard methods of processing errors, such as recording critical errors in the error log. If SQL Server is in critical need of attention and your operators do not possess pagers, SQL Server can send e-mail to them. This approach is also practical for notifying administrators of successfully completed jobs.

Another common use is to process e-mail that contains database queries. Remote users can send queries to SQL Server and have it return result sets to them.

SQL Server can also send messages that include result sets in the form of a report to one or more users. Although these result sets are rather crude (just ASCII text), it is possible to envision and create an application that uses this capability to notify management when some change occurs in the database (however, it probably makes more sense to create such applications using Reporting Services or Service Broker).

SQL Server contains several services that handle e-mail. I will not go into detail on the implementation and configuration of these services. Refer to SQL Server Books OnLine and the Microsoft Support web site for more details. We will just explore usage of the service called Database Mail.

Database Mail

This is a new feature introduced in SQL Server 2005. It is a robust and high-performance service designed to provide enterprise-level capability for sending and managing e-mails (through external SMTP servers). It is designed to be

• Scalable It provides background asynchronous delivery implemented through Service Broker.

• Reliable It is implemented as a process isolated from the rest of SQL Server that supports server clustering and failover of SMTP servers, and one that does not require Outlook or Extended MAPI as alternate technologies, as in previous versions of SQL Server.

• Secure It is turned off by default and is fully manageable when in use. For example, only members of the msdb role DatabaseMailUserRole (or its parents) are allowed to send e-mails; it is possible to limit the type and size of attachments.

• Supportable It is integrated in SQL Server Database Engine and manageable from Management Studio or Transact-SQL. It is possible to log all events and audit all messages.

Configuring Database Mail

Before you can use Database Mail, you must install it and enable it:

1. Enable Database Mail from the SQL Server Surface Area configuration (Start | Programs | SQL Server 2005 | Configuration Tools). Alternatively, you can use

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

2. Now configure it using the Database Mail Configuration Wizard:

   1. Open the context menu of Management Studio | Server | Management | Database Mail.

   2. Press Next on the first page.

   3. Make sure that Setup Database Mail is selected on the next page and press Next.

   4. On the New Profile page, set the Profile Name:

      

3. Click Add and fill the parameters of the mail account:

   

4. You can create more than one (failover) SMTP account. Click OK.

5. On the Manage Profile Security page, define which profiles are public and which user accounts have access to which private profiles, and then click Next.

6. On the next page, set the rest of the system configuration parameters:

   

7. When you click Next on the following page, Management Studio will get Database Mail ready for you to dispatch e-mails.

Note 

If you never tried to set mail features of earlier versions of SQL Server, you may consider this simple and straightforward. If you did, you will appreciate the work that Microsoft has invested in this version to simplify configuration.

Sending E-mails

The stored procedure msdb.dbo.sp_send_dbmail places specified e-mail in the Database Mail queue and returns an ID of the message in the queue to the caller. Service Broker manages the queue, reads messages one by one, and processes them.

The following batch sends a simple text message:

 Declare @Mailitem_Id Int Exec @Mailitem_Id = Msdb.Dbo.Sp_Send_Dbmail         @profile_name = 'Asset Admin',         @recipients = '[e-mail]',         @body = 'The ETL Procedure Completed Successfully.',         @subject = 'Automated Completion Message'; Select @mailitem_id [@mailitem_id] 

The following batch sends a message that contains a result of a query as an attachment:

 Declare @mailitem_id int EXEC @mailitem_id = msdb.dbo.sp_send_dbmail     @profile_name = 'Asset Admin',     @recipients = '[e-mail]',     @query = 'SELECT * FROM Assets.dbo.OrderHeader               WHERE TargetDate > Convert(smalldatetime.               Convert(varchar(30), DateAdd(d, 1, GetDateO), 101))               AND OrderStatusId  = 1',     @attach_query_result_as_file = 1 ; select @mailitem_id [@mailitem_id] 

Note 

Database Mail will accept messages in the queue even when Database Mail is not processing messages. You can start Database Mail using the msdb.dbo.sysmail_start_sp stored procedure or stop it using msdb.dbo.sysmail_stop_sp.

Check Status

You can examine the status of the message that was placed in the queue by running a query with mailitem_id, which was returned by msdb. dbo. sp_send_dbmail:

 select * from msdb.dbo.sysmail_allitems where mailitem id = 123