xp_sendmail (Transact-SQL)

Sends an e-mail message, which may include a query result set attachment, to the specified recipients. This extended stored procedure uses SQL Mail to send the message.

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. To send mail from SQL Server, use Database Mail.

Transact-SQL Syntax Conventions

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.

• [ @message=] 'message'
  Is the message body to be sent. messagecan be up to 8,000 bytes.

• [ @query=] 'query'
  Is a valid 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 inserted and deleted logical tables, because these tables are only available within a trigger. querycan be up to 8,000 bytes.

• [ @attachments=] **'**attachments [ ;... n] '
  Is a semicolon-separated list of files to attach to the mail message. If you use the @query parameter when @attach_results is TRUE, the @attachments parameter can specify only one file to attach to the mail message. In this case, to send more than one file, you must run xp_sendmail separately for each attached file.

• [ @copy_recipients=] **'**copy_recipients [ ;... n] '
  Is the semicolon-separated list identifying the recipients of a copy of the mail.

• [ @blind_copy_recipients=] **'**blind_copy_recipients[ ;... n] '
  Is an optional semicolon-separated list identifying recipients of a blind copy of the mail.

• [ @subject=] 'subject'
  Is a parameter specifying the subject of the mail. If subjectis 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, xp_sendmail uses a message type of IPM. Message types that start with IPM appear in the inbox of the mail client and are found or read by xp_findnextmsg. Message types that start 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. SQL Mail supports message types of IPM and IPC.

• [ @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 parsing of the result set in 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 running 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

When successful, xp_sendmail returns a message.

Remarks

The SQL Mail session must be started before xp_sendmail is run. Sessions can be started either automatically or with xp_startmail. For more information about how to set up a SQL Mail session automatically, see Configuring Extended MAPI Mail Profiles. One SQL Mail session supports all users for the SQL Server instance, 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 runs 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 the row by the initial client connection.

xp_sendmail runs in the security context of SQL Server. A valid user of xp_sendmail can access files for attachment to a mail message in an administrator's security context. If users other than system administrators 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 which 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 it 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 run xp_sendmail when the post office and address book are on a file share that the MSSQLServer service cannot access because of inadequate permissions.

xp_sendmail does not fully support the xml data type introduced in SQL Server 2005. Queries that use the xml data type may be formatted incorrectly. Use Database Mail to send e-mail that includes xml data.

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

A. Sending a message to a single recipient

The following example sends a message to user Dan Wilson (e-mail is danw) that the master database is full.

EXEC master.dbo.xp_sendmail 
    @recipients=N'danw@Adventure-Works.com',
    @message=N'The master database is full.' ;

B. Sending a message to multiple recipients

The following example sends the message to users Dan Wilson and Ashvini Sharma (e-mail is ashvinis), with a copy sent to Peter Connelly (e-mail is peterc). The example also specifies a subject line for the message.

EXEC master.dbo.xp_sendmail 
    @recipients=N'danw@Adventure-Works.com;ashvinis@Adventure-Works.com',
     @message=N'The master database is full.',
     @copy_recipients=N'peterc@Adventure-Works.com',
     @subject=N'Master database status' ;
GO

C. Sending results

The following example sends the results of sp_configure to Dan Wilson.

EXEC master.dbo.xp_sendmail 
    @recipients=N'danw@Adventure-Works.com',
    @query = N'EXEC sp_configure' ;
GO

D. Sending results as an attached file

The following example sends the results of the query SELECT * FROM INFORMATION_SCHEMA.TABLES as a text file attachment to Dan Wilson. The example 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 master.dbo.xp_sendmail
    @recipients = N'danw@Adventure-Works.com', 
    @query = N'SELECT * FROM INFORMATION_SCHEMA.TABLES',
    @subject = N'SQL Server Report',
    @message = N'The contents of INFORMATION_SCHEMA.TABLES:',
    @attach_results = 'TRUE',
    @width = 250 ;

E. Sending messages longer than 7,990 byte characters

The following example shows how to send a message longer than 7,990 byte 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 that is made up of a single text column. The contents of this temporary table are then sent in mail by using the @query parameter.

CREATE TABLE ##mail_body(c1 NVARCHAR(4000)) ;

DECLARE @cmd VARCHAR(56) ;

INSERT ##mail_body(c1)
VALUES ('Put your long message here.') ;

SET @cmd = 'SELECT c1 FROM ##mail_body' ;

EXEC master.dbo.xp_sendmail 
    @recipients = 'danw@Adventure-Works.com', 
    @query = @cmd,
    @no_header= 'TRUE' ;

DROP TABLE ##mail_body ;

See Also

Reference

sp_processmail (Transact-SQL)
System Stored Procedures (Transact-SQL)
xp_deletemail (Transact-SQL)
xp_findnextmsg (Transact-SQL)
xp_readmail (Transact-SQL)
xp_startmail (Transact-SQL)
xp_stopmail (Transact-SQL)
sp_send_dbmail (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance