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