SQL Mail

SQL Mail has been around for several releases of SQL Server and has not undergone significant changes since SQL Server 2000. It is asynchronous in nature: that is, each call to the underlying mailing infrastructure is blocked until the requested operation is completed. SQL Mail uses Extended MAPI for mail delivery, so it needs to be installed and configured outside of SQL Server Database Engine before SQL Mail actually can be used.

Overview

SQL Mail is a collection of extended stored procedures for sending, reading, and deleting mail items that share the same MAPI profile. There is also a regular T-SQL stored procedure (sp_process_mail) that calls several extended stored procedures to perform work. Table 10-1 lists all the procedures and describes their purposes.

Table 10-1. SQL Mail Stored Procedures

Stored Procedure Name	Main Function
xp_startmail	Initializes an extended MAPI mail session and logs on to the mail server.
xp_stopmail	Closes the MAPI mail session and performs mail server log off.
xp_sendmail	Sends mail, optionally executing T-SQL queries and formatting results to be sent as attachments or body text.
xp_readmail	Reads mail messages from the mail box.
xp_findnextmsg	Navigates mail messages by finding the next message after given one.
xp_deletemail	Deletes mail messages from the mail box.
sp_process_mail	Reads and deletes all the messages in the inbox, converting mail items into resultsets and returning them to the caller.
xp_get_mapi_default_profile	Retrieves the default MAPI profile.
xp_get_mapi_profiles	Enumerates MAPI profiles accessible to the SQL Server Database Engine service account.
xp_test_mapi_profile	Performs MAPI profile validation by logging on and off the mail server.
xp_issqlmailstarted	Checks whether a mail session has been initialized.

By default, only members of the sysadmin fixed server role have execute permissions for any of the SQL Mail procedures, but these permissions can be granted to other users.

Tips and Tricks

Most users need to have permission to only send mail, so it is sufficient to grant them the xp_sendmail procedure with the execute privilege.

The SQL Mail component is OFF by default on a brand new installation of SQL Server 2005 and needs to be explicitly enabled before the first use. If, however, SQL Server Database Engine has been upgraded from a SQL Server 2000 instance, the component remains enabled to provide full backward compatibility.

Before we consider different mailing scenarios that use SQL Mail, it is worthwhile to spend some time talking about SQL Mail configuration.

SQL Mail requires extended MAPI to be deployed on the machine from which mail is going to be sent, that is, where the SQL Server Database Engine instance is installed. It's worth noting that because of its reliance on extended MAPI, SQL Mail does not work on any 64-bit platform. The simplest supported way to install extended MAPI is through a Microsoft Outlook installation.

Tips and Tricks

Microsoft has released multiple versions of extended MAPI. It is very important to know exactly which version is used by SQL Mail. Although Outlook's MAPI will work, many enterprise customers prefer using versions of MAPI shipped with Exchange server because it may be more robust and scalable. However, these versions are not supported by PSS.

After extended MAPI is deployed, you need to configure the email profile to be used by SQL Mail. Because SQL Mail will be running inside a SQL Server Database Engine process, SQL Mail accesses email facilities using service account credentials. Thus, if you intend to use Outlook configuration facilities, you need to configure the email profile while logging in to the Windows server under this account. You can configure Outlook 2003 mail account settings by using the Custom Installation Wizard at installation time, and then change them later using the Custom Maintenance Wizard.

When configuring email accounts you can set up multiple ones and then select which one to use via the xp_startmail extended procedure, passing its profile name as a parameter. If the parameter is missing, SQL Mail uses the default profile configured for the user.

Setting up and testing the default profile for SQL Mail can also be done through SQL Server Management Studio, as shown in Figure 10-1. To bring up the SQL Mail Configuration dialog, you need to right-click on the SQL Mail folder and select Properties.

Figure 10-1. SQL Mail configuration.

Tips and Tricks

If you want SQL Mail to use the default account, the xp_startmail call can be omitted all together. Mail session initialization will be done as part of the first xp_sendmail call.

Calling the xp_stopmail extended procedure has the reverse effect of xp_startmail; it closes the MAPI session and allows the user to change the mail profile on the next xp_startmail call.

Tips and Tricks

Frequent calling of xp_startmail/xp_stopmail is not recommended because of architectural design limitations of SQL Mail and can cause memory and handle leaks inside the SQL Server Database Engine process.

Sending Mail

After the mail session is initialized, you are ready to send mail. Let's examine different options of the xp_sendmail extended stored procedure. Here is the accepted syntax.

xp_sendmail { [ @recipients= ] 'recipients [ ;...n ]' }      [ ,[ @message= ] 'message' ]      [ ,[ @query= ] 'query' ]      [ ,[ @attachments= ] 'attachments [ ;...n ]' ]      [ ,[ @copy_recipients= ] 'copy_recipients [ ;...n ]'      [ ,[ @blind_copy_recipients= ] 'blind_copy_recipients [ ;...n ]'      [ ,[ @subject= ] 'subject' ]      [ ,[ @type= ] 'type' ]      [ ,[ @attach_results= ] 'attach_value' ]      [ ,[ @no_output= ] 'output_value' ]      [ ,[ @no_header= ] 'header_value' ]      [ ,[ @width= ] width ]      [ ,[ @separator= ] 'separator' ]      [ ,[ @echo_error= ] 'echo_value' ]      [ ,[ @set_user= ] 'user' ]      [ ,[ @dbuse= ] 'database' ] 

One of the most important options is specifying the query to be executed against Database Engine. When this parameter is supplied, the mail procedure establishes an internal connection to SQL Server Database Engine under service account credentials, changes the security context to the caller, and then runs the query. Results of the query get formatted and included in the message body. Here is an example:

EXEC master.dbo.xp_sendmail   @recipients = N' [e-mail]; [e-mail]',   @subject = N' contents of the authors table',   @query = N' select * from pubs..authors' GO 

Notice how email addresses for multiple users are separated with a semicolon. If the table name was not fully qualified, you could have specified the @dbuse parameter instead.

Query results can also be attached as an output file, as in the following example:

EXEC master.dbo.xp_sendmail   @recipients = N'[e-mail]; [e-mail]',   @query = N'select * from pubs..authors',   @subject = N'contents of the authors table',   @attach_results = 'TRUE',   @attachments = N'query_results.log' GO 

If the @attachments parameter was not supplied, the output file name would be automatically generated and have a .txt extension. The @attachments parameter specification also allows SQL Mail to attach existing files to the email message. If users attempting to attach files are not members of the sysadmin fixed server role, SQL Server Database Engine impersonates the user before accessing the file system. If the user is connected to SQL Server through SQL Authentication, he is not able to attach files at all.

Note

This behavior is different from SQL Server 2000, where a global proxy account was used to validate permission access to the file for non-sysadmin users.

Another parameter that deserves special consideration is @set_user. If this parameter is supplied, SQL Mail issues a SETUSER request to SQL Server Database Engine before query execution takes place. However, for the SETUSER statement to succeed, the caller needs to have sufficient privileges for impersonation, or the SETUSER statement will fail.

Most other options of xp_sendmail have to do with query results formatting and need be experimented with only when you are trying to conform the desired output to a template or make it easily parsable by automated tools.

In general, it's worth noting that SQL Mail processes query execution, formatting, and sending the mail message synchronously with the user request. Thus, the xp_sendmail procedure does not return until the message is either successfully sent or has failed in formatting or delivery. Sometimes, the back-end server can experience a temporary outage or network connectivity issue that will interfere with mail delivery, and this might cause the SQL Mail thread to appear hung. This has been a source of numerous customer complaints and could not be fully rectified without re-designing the SQL Mail solution. This problem was the driving force behind creation of a different type of asynchronous solution in SQL Server 2005, based on the SMTP mail delivery protocol, called Database Mail. Database Mail design and architecture are examined later in this chapter.

Reading Mail

Before you can call xp_readmail, you need to obtain a valid message ID. This is done via the xp_findnextmsg extended stored procedure. Here is the accepted syntax:

xp_findnextmsg [ [ @type= ] type ]      [ , [ @unread_only= ] 'unread_value ' ]      [ , [ @msg_id= ] 'message_id' [ OUTPUT ] ] 

@msg_id is actually an input as well as an output parameter into the procedure. On the very first call its value is going to be NULL and from that point on it will be populated with the ID of the next message in the inbox waiting to be read.

After receiving a valid (non-NULL) message ID, you can call xp_readmail with the following syntax:

xp_readmail [ [ @msg_id= ] 'message_id' ]      [ , [ @type= ] 'type' [ OUTPUT ] ]      [ , [ @peek= ] 'peek' ]      [ , [ @suppress_attach= ] 'suppress_attach' ]      [ , [ @originator= ] 'sender' OUTPUT ]      [ , [ @subject= ] 'subject' OUTPUT ]      [ , [ @message= ] 'message' OUTPUT ]      [ , [ @recipients= ] 'recipients [ ;...n ]' OUTPUT ]      [ , [ @cc_list= ] 'copy_recipients [ ;...n ]' OUTPUT ]      [ , [ @bcc_list= ] 'blind_copy_recipients [ ;...n ]' OUTPUT ]      [ , [ @date_received= ] 'date' OUTPUT ]      [ , [ @unread= ] 'unread_value' OUTPUT ]      [ , [ @attachments= ] 'attachments [ ;...n ]' OUTPUT ])      [ , [ @skip_bytes= ] bytes_to_skip OUTPUT ]      [ , [ @msg_length= ] length_in_bytes OUTPUT ]      [ , [ @originator_address= ] 'sender_address' OUTPUT ] ] 

As you can see, there are only a handful of input parameters for this procedure other than @msg_id. @peek is used to flag the message as read if the value is FALSE, or leave it unread if set to TRUE. @suppress_attach avoids creation of temporary attachment files on the server when the mail message is read if set to TRUE.

Finally, after reading the email message, you can delete it by calling xp_deletemail with the following syntax:

xp_deletemail { 'message_id' } 

For a good example of how to use these three procedures (xp_ findextmsg, xp_readmail, and xp_deletemail) you can look at the contents of stored procedure sp_processmail for putting them all together.

Tips and Tricks

To see the contents of sp_processmail, execute the following command from the Query Editor tool:

sp_helptext 'sp_processmail'