SQL Server 2000 provides built-in stored procedures and extended stored procedures that you can use to process e-mail messages received in a predefined SQL Mail account mailbox or to send e-mail messages from within your own SQL code. The following sections describe the stored procedures (with their corresponding syntax) that can be run individually or executed within the instructions of another stored procedure.
You use the xp_startmail procedure to start a mail client session. This procedure should be executed first to ensure that a SQL Mail session is running. The syntax for xp_startmail is as follows :
xp_startmail [@user = ' username '], [@password = ' password ']
' username ' is optional; when it's specified, the SQL Server attempts to log on to the MAPI provider using this name .
' password ' is optional; when specified, it's used as the password for the ' username ' .
' username ' and ' password ' are both optional parameters defined with the sysname datatype and no default value. If these arguments are not provided, SQL Server will use the default mail profile associated with SQL Mail.
Permission to execute xp_startmail defaults to members of the sysadmin fixed server role, but can be granted to any SQL Server user. Only one mail session can be active at a time; however, after a SQL Mail session is started, all users can use the same mail session. If an existing mail session is already active, xp_startmail will not start a new one and will return a message indicating that it is already running, as shown in the following example:
declare @rval int exec @rval = xp_startmail --'Outlook' select @rval as rval go SQL Mail session is already started. rval ----------- 1
You can use xp_stopmail to stop the current mail session.
You use the xp_stopmail procedure to stop the current mail client session. The syntax is simple:
Permission to execute xp_stopmail defaults to members of the sysadmin fixed server role, but can be granted to any SQL Server user.
The xp_sendmail stored procedure is used to send messages or query result sets to specified mail recipients. When invoking xp_sendmail , only the @recipients parameter is required; all other parameters are optional. To specify subsequent parameters, you will have to specify the parameters by name if you want to skip any. The full syntax for xp_sendmail is as follows:
xp_sendmail @recipients = ' recipients [;... n ]' ,[@message = ' message '] ,[@query = ' query '] ,[@attachments = attachments ] ,[@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 ]
Table 23.1 describes each element of the syntax.
Table 23.1. xp_sendmail Parameters
Only one user at a time can send an e-mail through the active SQL Mail session. Other users who are sending mail messages at the same time will wait in a queue until the previous user's mail message has been sent.
Permission to execute xp_sendmail defaults to members of the sysadmin fixed server role, but can be granted to any SQL Server user.
The following example sends an e-mail message to the SQL Operators distribution list with the output from sp_who2 as an attachment, setting the width of the query output to 255 bytes:
exec master..xp_sendmail 'SQL Operators', 'The attached file shows all current user activity on the server', 'exec sp_who2', @subject = 'Current User Activity', @width = 255, @attach_results = 'True'
The xp_findnextmsg procedure is used to find the next message to be read from the mail profile's Inbox. It returns the message ID as an output parameter or a result row. The syntax is as follows:
xp_findnextmsg [@msg_id = ' message_number ' [OUTPUT]] ,[@type = type] ,[@unread_only = ' unread_value ']
The following paragraphs describe the elements of the syntax:
[@msg_id = ' message_number ' [OUTPUT]]
This is the number of the message, returned as a varchar(255) . When the OUTPUT clause is used, the message number is placed in this parameter. Otherwise, it is returned as a result set.
[@type = type ]
This is the MAPI mail type. The default is NULL .
[@unread_only = ' unread_value ']
When this is set to True , only unread messages are processed by the stored procedure. The default is False .
The following is an example that returns the message ID of the next unread message in the Inbox:
exec xp_findnextmsg @unread_only = 'true' go Message ID ------------------------------------------------------------------------------ 0xE4D12200
The xp_readmail procedure reads a mail message from the SQL Server Inbox. You can use xp_readmail to return the contents of the Inbox as a result set to the client or to read a single message from the Inbox. The syntax for xp_readmail is as follows:
xp_readmail [@msg_id = ' message_number '] ,[ @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]
When the OUTPUT keyword is specified for one or more of the xp_readmail parameters, the resulting value is returned to this parameter.
Table 23.2 describe each element of the syntax:
Table 23.2. xp_readmail Parameters
To return the entire contents of the Inbox as a result set to the client, either set message_number to NULL or do not include the message_number parameter. If you want to limit the messages returned, the @msg_id can be used to read specific messages. You can also specify the @peek and @suppress_attach as input parameters to control how the message is read.
If using xp_readmail without specifying @msg_id to return the Inbox contents, xp_readmail returns a result set containing the mail messages (older messages appear first) with the columns, as shown in Table 23.3.
Table 23.3. xp_readmail Result Columns
To read a single message from the Inbox, you need to supply a valid message number as an input parameter to xp_readmail . You can obtain the message number using the xp_findnextmsg procedure. When reading a single message, you can use the output parameters to return the specified information about the message into local variables, as shown in the following example:
declare @msg varchar(8000), @msgid varchar(255), @subject varchar(255), @sender varchar(255) exec xp_findnextmsg @unread_only = 'true', @msg_id = @msgid output if @msgid is NOT NULL -- make sure we have a message to read begin exec xp_readmail @msgid, @peek = 'True', @subject = @subject output, @originator = @sender output, @message = @msg output print 'Subject: ' + @subject print 'Sent By: ' + @sender print '------------------------------' print 'Message: ' + @msg end go Subject: Microsoft SQL Server Alert System Test Sent By: Ray Rankins ------------------------------ Message: Microsoft SQLServerAgent test notification - please ignore.
The xp_deletemail procedure deletes the specified message from the SQL Server Inbox. The syntax is as follows:
xp_deletemail ' message_number '
' message_number ' is the number, specified as varchar(255) , of the message that is to be deleted.
The sp_processmail procedure invokes the extended-stored procedures xp_findnextmsg , xp_readmail , and xp_deletemail to read and process incoming mail messages that are expected to contain a SQL query. The messages are read from the Inbox of the mail profile configured for SQL Server. Any result sets generated by the queries processed are sent back to the message sender using xp_sendmail . Typically, you will set up sp_processmail in a regularly scheduled job to periodically check for mail received in the SQL Mail Inbox.
The syntax is as follows:
sp_processmail [@subject = ' subject '], [@filetype = ' filetype '], [@separator = ' separator '], [@set_user = ' user '], [@dbuse = ' dbname ']
Table 23.4 describes the parameters for the sp_processmail procedure.
Table 23.4. sp_processmail Parameters
The following example uses one session to send an e-mail containing a query to the e-mail account used by SQL Mail, and the next session uses sp_processmail to read the e-mail, execute the query, and return the result set as an e-mail attachment to the originating e-mail account:
xp_sendmail 'SQLAdmin', 'exec sp_who2', @subject = 'Run This Query' go exec sp_processmail @subject = 'Run This Query' go Mail sent. Queries processed: 1.