Transact-SQL Reference

xp_sendmail

Sends a message and a query result set attachment to the specified recipients.

Syntax

xp_sendmail {[@recipients =] 'recipients [;...n]'}
    [,[@message =] 'message']
    [,[@query =] 'query']
    [,[@attachments =] 'attachments [;...n]']
    [,[@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']

Arguments

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

Is the semicolon-separated list of the recipients of the mail.

Is a placeholder indicating that more than one recipient, copy_recipient, or blind_copy_recipient can be specified.

[@message =] 'message'

Is the message to be sent. message can be up to 8,000 bytes.

[@query =] 'query'

Is a valid Microsoft® SQL Server™ query, the result of which is sent in mail. xp_sendmail uses a bound connection for the query parameter. The query connection made by SQL Mail is not blocked by locks held by the client that issues the xp_sendmail request. This makes xp_sendmail easier to use from within triggers. The query statement, however, cannot refer to the logical inserted and deleted tables that are only available within a trigger. query can be up to 8,000 bytes.

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

Is a semicolon-separated list of files to attach to the mail message.

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

Is the semicolon-separated list identifying the recipients of a copy of the mail (cc:'ing).

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

Is an optional semicolon-separated list identifying recipients of a blind copy of the mail (bcc:'ing).

[@subject =] 'subject'

Is an optional parameter specifying the subject of the mail. If subject is not specified, SQL Server Message is the default.

[@type =] 'type'

Is the input message type based on the MAPI mail definition:

IP[M | C].Vendorname.subclass

If type is NULL, message types beginning with IPM appear in the inbox of the mail client and are found or read by xp_findnextmsg. Message types beginning with IPC do not appear in the inbox of the mail client and must be found or read by setting the type parameter. The default is NULL.

For more information about using custom message types, see the Microsoft Windows NT Resource Kit or the Microsoft Mail Technical Reference, available separately.

[@attach_results =] 'attach_value'

Is an optional parameter specifying the result set of a query should be sent in mail as an attached file instead of being appended to the mail. If attachments is not NULL and attach_results is true, the first file name in attachments is used as the file name for the results. If attachments is NULL, a file name is generated with a .txt extension. The default is FALSE, which means that the result set is appended to the message.

[@no_output =] 'output_value'

Is an optional parameter that sends the mail but does not return any output to the client session that sent the mail. The default is FALSE, which means that the client session of SQL Server receives output.

[@no_header =] 'header_value'

Is an optional parameter that sends the query results in mail but does not send column header information with the query results. The default is FALSE, which means that column header information is sent with the query results.

[@width =] width

Is an optional parameter setting the line width of the output text for a query. This parameter is identical to the /w parameter in the isql utility. For queries producing long output rows, use width with attach_results to send the output without line breaks in the middle of output lines. The default width is 80 characters.

[@separator =] 'separator'

Is the column-separator string for each column of the result set. By default, the column-separator is a blank space. Use of a column-separator allows easier accessibility of the result set from spreadsheets and other applications. For example, use separator with attach_results to send files with comma-separated values.

[@echo_error =] 'echo_value'

When true, causes SQL Mail to capture any server messages or DB-Library errors encountered while running the query and append them to the mail message rather than writing them to the error log. Also, a count of rows returned/rows affected is appended to the mail message.

Note When echo_error is true, xp_sendmail returns a status of 0 (success) if the mail is successfully sent, even if DB-Library errors or messages are encountered or the query returns no results.

[@set_user =] 'user'

Is the security context in which the query should be run. If user is not specified, the security context defaults to that of the user executing xp_sendmail.

[@dbuse =] 'database'

Is the database context in which the query should be run. The default is NULL, which means the user is placed in the default database.

Return Code Values

0 (success) or 1 (failure)

Result Sets

xp_sendmail returns this message:

Mail sent.

Remarks

The SQL Mail session must be started prior to executing xp_sendmail. Sessions can be started either automatically or with xp_startmail. For more information about setting up a SQL Mail session automatically, see Configuring Mail Profiles. One SQL Mail session supports all users on the SQL Server, but only one user at a time can send a message. Other users sending mail messages automatically wait their turns until the first user's message is sent.

If query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQL Mail makes a separate connection to SQL Server; it does not share the same connection as the original client connection issuing xp_sendmail.

Note query can be blocked by a lock held by the client connection issued xp_sendmail. For example, if you are updating a table within a transaction and you create a trigger for update that attempts to select the same updated row information as the query parameter, the SQL Mail connection is blocked by the exclusive lock held on row by the initial client connection.

xp_sendmail runs in SQL Server's security context, which is a local administrator account by default. A valid user of xp_sendmail can access files for attachment to a mail message in an administrator's security context. If nonsystem administrator users must access xp_sendmail and you want to guard against unsecured access to attachment files, the system administrator can create a stored procedure that calls xp_sendmail and provides the needed functionality but does not expose the attachments parameter. This stored procedure must be defined in the master database. The system administrator then grants execute permission on the stored procedure to the necessary users without granting permission to the underlying xp_sendmail procedure.

xp_sendmail sends a message and a query result set or an attachment to specified recipients, and uses a bound connection for the query parameter. The query connection made by SQL Mail is not blocked by locks held by the client that issues the xp_sendmail request. This makes xp_sendmail easier to use from within triggers. The query statement, however, cannot refer to the logical inserted and deleted tables that are only available within a trigger.

Note An access violation can result from an attempt to execute xp_sendmail when the post office and address book are on a file share that the MSSQLServer service cannot access due to inadequate permissions.

For more information about using a stored procedure for calling xp_sendmail, see How to use SQL Mail (Transact-SQL).

Permissions

Execute permissions for xp_sendmail default to members of the db_owner fixed database role in the master database and members of the sysadmin fixed server role, but can be granted to other users.

Examples

A. Use xp_sendmail with no variables

This example sends a message to user Robert King (e-mail is robertk) that the master database is full.

EXEC xp_sendmail 'robertk', 'The master database is full.'

B. Use xp_sendmail with variables

This example sends the message to users Robert King and Laura Callahan (e-mail is laurac), with copies sent to Anne Dodsworth (e-mail is anned) and Michael Suyama (e-mail is michaels). It also specifies a subject line for the message.

EXEC xp_sendmail @recipients = 'robertk;laurac', 
   @message = 'The master database is full.',
   @copy_recipients = 'anned;michaels',
   @subject = 'Master Database Status'

C. Send results

This example sends the results of the sp_configure to Robert King.

EXEC xp_sendmail 'robertk', @query = 'sp_configure'

D. Send results as an attached file

This example sends the results of the query SELECT * FROM INFORMATION_SCHEMA.TABLES as a text file attachment to Robert King. It includes a subject line for the mail and a message that will appear before the attachment. The @width parameter is used to prevent line breaks in the output lines.

EXEC xp_sendmail @recipients = 'robertk', 
   @query = 'SELECT * FROM INFORMATION_SCHEMA.TABLES',
   @subject = 'SQL Server Report',
   @message = 'The contents of INFORMATION_SCHEMA.TABLES:',
   @attach_results = 'TRUE', @width = 250

E. Send messages longer than 7,990 characters

This example shows how to send a message longer than 7,990 characters. Because message is limited to the length of a varchar (less row overhead, as are all stored procedure parameters), this example writes the long message into a global temporary table consisting of a single text column. The contents of this temporary table are then sent in mail using the @query parameter.

CREATE TABLE ##texttab (c1 text)
INSERT ##texttab values ('Put your long message here.')
DECLARE @cmd varchar(56)
SET @cmd = 'SELECT c1 FROM ##texttab'
EXEC master.dbo.xp_sendmail 'robertk', 
   @query = @cmd, @no_header= 'TRUE'
DROP TABLE ##texttab