xp_readmail Extended Stored Procedure

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

Syntax

xp_readmail ([@msg_id = msg_id] [, @type = type [OUTPUT]]
    [, @peek = {'true' | 'false'}]
    [, @suppress_attach = {'true' | 'false'}]
    [, @originator = @sender OUTPUT]
    [, @subject = @subject_line OUTPUT]
    [, @message = @body_of_message OUTPUT]
    [, @recipients = @recipient_list OUTPUT]
    [, @cc_list = @cc_list OUTPUT]
    [, @bcc_list = @bcc_list OUTPUT]
    [, @date_received = @date OUTPUT]
    [, @unread = {'true' | 'false'}]
    [, @attachments = @temp_file_paths OUTPUT])
    [, @skip_bytes = @bytes_to skip OUTPUT]
    [, @msg_length = @length_in_bytes OUTPUT])

where

@msg_id = msg_id

Specifies the ID of the message to read.

@type = type

Returns the message class based on the MAPI mail definition:

IP[M | C].Vendorname.subclass

If used on input, this must define the type for a specific message; this parameter is ignored on input if the msg_id is NULL.

The default type is NULL.

@peek = {'true' | 'false'}

Allows SQL Server to return the message of the mail without changing the mail status to read. The default is false (treat the mail as though it has been read).

@suppress_attach = {'true' | 'false'}

Suppresses mail attachments. When set to true, prevents the creation of temporary files when xp_readmail reads a message with attachments. The default is true (do not create temporary files).

@originator = @sender

Is the variable in which the mail address of the sender will be returned. Define this variable as varchar(255).

@subject = @subject_line

Is the variable in which the subject of the mail message will be returned. Define this variable as varchar(255).

@message = @body_of_message

Is the variable in which the body (the actual text) of the mail message will be returned. Define this variable as varchar(255).

@recipients = @recipient_list

Is the variable in which the list of the recipients of the mail message will be returned. Recipients' names are separated by a semicolon (;). Define this variable as varchar(255).

@cc_list = @cc_list

Is the variable in which the list of the copied recipients (cc:'ed) of the mail message will be returned. Recipients' names are separated by a semicolon (;). Define this variable as varchar(255).

@bcc_list = @bcc_list

Is the variable in which the list of the blind copy recipients (bcc:'ed) of the mail message will be returned. Recipients' names are separated by a semicolon (;). Define this variable as varchar(255).

@date_received = @date

Is the variable in which the date of the mail message will be returned. Define this variable as varchar(255).

@unread = 'true' | 'false'

Specifies that only unread messages are considered. The default is true.

@attachments = @temp_file_paths

Is the variable in which the temporary paths of the mail attachments will be returned. Temporary paths are separated by a semicolon (;). Define this variable as varchar(255).

@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 @skip_bytes is used, @body_of_message will include the next portion of the message and @bytes_to_skip will return with the next starting point within the message (the previous @bytes_to_skip plus the length of @body_of_message).

@msg_length = @length_in_bytes OUTPUT

Returns the total length of the message, in bytes. When used with @skip_bytes in a stored procedure, this parameter will allow messages to be read in chunks of 255 bytes.

Remarks

This extended stored procedure returns a successful (0) or failed (1) status. Any failure except an invalid parameter is logged to the Windows NT application event log.

Example

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

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'

Permission

Execute permission defaults to the system administrator, who can grant permission to other users.

See Also