xp_readmail (T-SQL)

Reads a mail message from the Microsoft® SQL Server™ mail inbox. This procedure is used by sp_processmail to process all mail in the SQL Server inbox.

Syntax

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]]

Arguments

[@msg_id =] 'message_number'

Is the number of the message to read. message_number is varchar(255), with no default.

'type'

Is the message type to return based on the MAPI mail definition:

IP[M | C].Vendorname.subclass

If used on input, this must define the type for a specific message; type is ignored on input if the message_number is NULL. type is varchar(255), with a default of NULL.

OUTPUT

When specified, places the value of the specified parameter in the output parameter.

[@peek =] 'peek'

Is whether SQL Server returns the message of the mail without changing the mail status to read. peek is varchar(5), with a default of FALSE. If set to false, the mail is treated as though it has been read. If set to true, the mail is treated as though it has not been read.

[@suppress_attach =] 'suppress_attach'

Is whether mail attachments are suppressed. suppress_attach is varchar(255), with a default of TRUE (do not create temporary files). If set to true, SQL Server prevents the creation of temporary files when xp_readmail reads a message with attachments. If set to false, there is no prevention of temporary files when messages with attachments are read.

[@originator =] 'sender'

Is the returned mail address of the sender. sender is varchar(255), with no default.

[@subject =] 'subject'

Is the returned the subject of the mail message. subject is varchar(255), with no default.

[@message =] 'message'

Is the returned body or the actual text of the mail message. message is varchar(8000), with no default.

[@recipients =] 'recipients [;...n]'

Is the semicolon-separated list of the recipients for the mail message to be returned. Recipients’ names are separated by a semicolon (;). recipient_list is varchar(255), with no default.

[@cc_list =] 'copy_recipients [;...n]'

Is the semicolon-separated list of the copied recipients (cc:’ed) for the mail message to be returned. Recipients’ names are separated by a semicolon (;). cc_list is varchar(255), with no default.

[@bcc_list =] 'blind_copy_recipients [;...n]'

Is the semicolon-separated list for the blind copy recipients (bcc:’ed) of the mail message to be returned. Recipients’ names are separated by a semicolon (;). bcc_list is varchar(255), with no default.

[@date_received =] 'date'

Is the returned date of the mail message. date is varchar(255), with no default.

[@unread =] 'unread_value'

Is whether a message has been previously unread (true) or not (false). unread_value is varchar(5), with a default of TRUE.

[@attachments =] 'attachments [;...n]'

Is the semicolon-separated list of returned temporary paths of the mail attachments for the message. Temporary paths are separated by a semicolon (;). attachments is varchar(255), with no default.

[@skip_bytes =] bytes_to_skip OUTPUT

If a value other than 0 is passed for input, this parameter specifies the number of bytes to skip before reading the next 255 bytes (max) of the message into the body of message output parameter. When bytes_to_skip is used, body_of_message includes the next portion of the message and bytes_to_skip returns with the next starting point within the message (the previous bytes_to_skip plus the length of message). bytes_to_skip is int, with a default of 0.

[@msg_length =] length_in_bytes OUTPUT

Is the total length of the message, in bytes. When used with bytes_to_skip in a stored procedure, this parameter allows messages to be read in chunks of 255 bytes. length_in_bytes is int, with a default of 255 (bytes).

[@originator_address =] 'sender_address'

Is the resolved mail address of the originator of the mail message. sender_address is varchar(255), with no default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

xp_readmail returns a result set with these columns (older messages appear first).

Column name	Description
Originator	Sender of e-mail message
Date Received	Date the e-mail message was received
Recipients	The people to whom the message was sent
CC List	The people on the CC’d line of the e-mail message
BCC List	The people on the BCC’d line of the e-mail message
Subject	Subject line of the e-mail message
Message	Message body (text)
Unread	Whether this message is unread
Attachments	Any attachments for the message
Message ID	Message ID
Type	Message type

Remarks

Any failure except an invalid parameter is logged to the Microsoft Windows NT® application log.

There are two ways to use xp_readmail:

Return the contents of the inbox as a result set to the client.
Read a single message from the inbox.

To return the contents of the inbox as a result set to the client either set message_number to NULL or do not include message_number. In this situation, type can be used to read specific messages. You can specify peek and suppress_attach as input parameters to control the way the message is read.

To read a single message from the inbox, supply a valid message_number returned by xp_findnextmsg as an input parameter to xp_readmail. You can specify peek and suppress_attach as input parameters to control the way the message is read. When using peek and suppress_attach with this method, all other parameters are optional output parameters containing specific information from the message to be read.

You can view an example of using xp_findnextmsg as an input parameter to xp_readmail by executing the following command:

sp_helptext 'sp_processmail'

When used to read a single message, xp_readmail can read message text of longer than 255 bytes in sections. Use length_in_bytes and length_in_bytes to read message text of longer than 255 bytes in sections. Using length_in_bytes as both an input and output parameter allows coding of a loop to process the entire message text. The following code shows an example of such a loop, assuming message_number is set to a valid message identifier returned by xp_findnextmsg.

USE master

WHILE (1 = 1)

BEGIN

EXEC @status = xp_readmail @msg_id = @msg_id, @message = @message OUTPUT, @skip_bytes = @skip_bytes OUTPUT,

@msg_length = @msg_length OUTPUT

IF @status <> 0 BREAK

SELECT 'msg_id' = @msg_id, 'msg_part' = @message

IF @skip_bytes = @msg_length BREAK

END

Permissions

Execute permissions for xp_readmail default to the members of the sysadmin fixed server role but can be granted to other users.

Examples

This example returns the status when reading a message. In this example, the value of a message ID from xp_findnextmsg is placed in the local variable @message_id and passed to xp_readmail.

USE master

EXEC @status = xp_readmail @msg_id = @message_id,

@originator = @originator OUTPUT,

@cc_list = @cc_list OUTPUT,

@subject = @msgsubject OUTPUT,

@message = @query OUTPUT,

@peek = 'TRUE',

@suppress_attach = 'TRUE'

sp_processmail	xp_startmail
xp_deletemail	xp_stopmail
xp_findnextmsg	System Stored Procedures (SQL Mail Extended Procedures)
xp_sendmail