Article
10/01/2010

xp_readmail (Transact-SQL)

Reads a mail message from the SQL Mail inbox. This procedure is used by sp_processmail to process all mail in the SQL Mail inbox.

Note

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Topic link icon Transact-SQL Syntax Conventions

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

Arguments

[ @msg_id = ] 'message_id'
Is the ID of the message to read. message_id is varchar(255), with no default.
[ @type = ] 'type'
Is the message type to return based on the MAPI 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 message_id is NULL. type is varchar(255), with a default of NULL. SQL Mail supports message types of IPM and IPC.
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_attachis varchar(255), with a default of FALSE. 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 sender. senderis 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 text, with no default.
[ @recipients = ] 'recipients [ ;... n] '
Is the semicolon-separated list of the recipients for the mail message to be returned. Recipient 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 recipients 'on the CC: field for the mail message to be returned. Recipient 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 recipients on the BCC: field of the mail message to be returned. Recipient' names are separated by a semicolon (;). bcc_list is varchar(255), with no default. If the e-mail server does not supply a value for the BCC field, then blind_copy_recipients is empty.
[ @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_skipOUTPUT
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_bytesOUTPUT
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.
[ @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.

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:" line of the e-mail message
BCC List	The people on the "BCC:" 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 Application Log.

There are two ways to use xp_readmail:

Return all messages in 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, do not provide any input parameters.

Failure to change the default for the suppress_attach parameter to TRUE raises two potential security issues for attachments.

First, if two different users share the same temporary directory and log on to the same computer, they will be able to view each other's attachments. You can determine where attachments are stored and whether two users share the same temporary directory by reviewing the attachments output variable.
Second, xp_deletemail does not delete these attachments, so you must delete each attachment manually.

To read a single message from the inbox, supply a valid message_id 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 to read message text of longer than 255 bytes in sections. Using length_in_bytes as both an input and an 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_id is set to a valid message identifier returned by xp_findnextmsg.

USE master;
GO

-- Set up variables.

DECLARE @status INT,
        @message_part VARCHAR(255),
        @msg_id VARCHAR(255),
        @message_length INT,
        @skip_bytes INT,
        @message VARCHAR(MAX) ;

-- Find the next message

EXEC @status = dbo.xp_findnextmsg
     @msg_id = @msg_id OUTPUT ;


-- If xp_findnextmsg completed successfully and
-- there is a message in the inbox, read the message.

IF (@status = 0  AND @msg_id IS NOT NULL)
BEGIN
   WHILE (1=1)
   BEGIN
        EXEC @status = dbo.xp_readmail
             @msg_id = @msg_id,
             @msg_length = @message_length OUTPUT,
             @skip_bytes = @skip_bytes OUTPUT,
             @message = @message_part OUTPUT ;

        IF @status <> 0 BREAK ;

        SET @message = ISNULL(@message, '') + @message_part ;

        PRINT @message_length ;
        PRINT @skip_bytes;

        IF @message_length = @skip_bytes BREAK ;

    END ;
    
    IF @status = 0
    BEGIN
       SELECT 'Message ID' = @msg_id, 'Message Body' = @message ;
    END ;
    ELSE
        SELECT 'Could not read message.' ;
END;

GO

Permissions

Requires membership in the sysadmin fixed server role, but EXECUTE permissions can be granted to other users. However, for security reasons, we recommend that permissions for this stored procedure be limited to members of the sysadmin fixed server role.

Examples

The following 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 ;
GO

DECLARE @status INT,
    @msg_id VARCHAR(255),
    @originator VARCHAR(255),
    @cc_list VARCHAR(255),
    @subject VARCHAR(255),
    @query VARCHAR(8000);


-- Find the next message

EXEC @status = dbo.xp_findnextmsg
     @msg_id = @msg_id OUTPUT ;


-- If xp_findnextmsg completed successfully and
-- there is a message in the inbox, read the message.

IF (@status = 0  AND @msg_id IS NOT NULL)
BEGIN
    EXEC @status = dbo.xp_readmail
        @msg_id = @msg_id,
        @originator = @originator OUTPUT,
        @cc_list = @cc_list OUTPUT,
        @subject = @subject OUTPUT,
        @message = @query OUTPUT,
        @peek = 'TRUE',
        @suppress_attach = 'TRUE'

END;
GO

Share via