xp_sendmail

(Windows only) Sends a message to the specified recipients. The message is either text or the results of a Transact-SQL query.

Syntax

xp_sendmail recipient [; recipient] . . .
	[, subject]
	[, cc_recipient] . . .
	[, bcc_recipient] . . . 
	[, {query | message}]
	[, attachname]
	[, attach_result = {true | false}]
	[, echo_error = {true | false}]
	[, include_file [, include_file] . . .]
	[, no_column_header = {true | false}]
	[, no_output = {true | false}]
	[, width]
	[, separator]
	[, dbuser]
	[, dbname]
	[, type]
	[, include_query = {true | false}]

Parameters

recipient – is the e-mail address of the user who will receive the message. At least one recipient is required. Separate multiple recipients with semicolons.
subject – is the optional message subject header.
cc_recipient – is a list of the message’s copied (cc’d) recipients (separated by semicolons).
bcc_recipient – is the list of the message’s blind- copied (bcc’d) recipients (separated by semicolons).
query – is one or more Transact-SQL statements. The results are sent to the recipients of the message. If query is used, message cannot be used.
message – is the text of the message being sent. If message is used, query cannot be used. For the complete list of options that are ignored when you use message, see the “Usage” section.
attachname – is the name of the file containing the results of a query, which is included as an attachment to the message, when the query parameter is used. If attachname is used, attach_result must be set to true. If attach_result is true and attachname is not specified, the prefix of the attached file’s generated file name is “syb” followed by 5 random digits followed by the “.txt” extension, for example, syb84840.txt. This parameter is ignored if the message parameter is used.
attach_result – if set to true, sends the results of a query as an attachment to the message. If set to false, sends the results directly in the message body. The default is false. This parameter is ignored if the message parameter is used.
echo_error – if set to true, sends the SAP ASE server messages, including the count of rows affected message, along with the query results. If set to false, does not send the SAP ASE server messages. The default is true. This parameter is ignored if the message parameter is used.
include_file – is a list of files to be included as attachments to the message, separated by semicolons. The files can be specified as file names, path names, or relative path names and can be either text or binary files.
no_column_header – if set to true, column headers are sent with query results. If set to false, column headers are not sent. The default is false. This parameter is ignored if the message parameter is used.
no_output – if set to true, no output is sent to the session that sent the mail. If set to false, the session sending the mail receives output. The default is false. This parameter is ignored if the message parameter is used.
width – specifies, in characters, the width of the results sets when query results are sent in a message. width is the same as the /w option in isql. Result rows are broken by the newline character when the specified width is reached. The default is 80 characters. This parameter is ignored if the message parameter is used.
separator – specifies the character to be used as a column separator when query results are sent in a message. separator is the same as the /s option in isql. The default is the tab character. This parameter is ignored if the message parameter is used.
dbuser – specifies the database user name to be assumed for the user context for executing queries when the query parameter is used. The default is “guest.” This parameter is ignored if the message parameter is used.
dname – specifies the database name to be assumed for the database context for executing queries when the query parameter is used. The default is “master.” This parameter is ignored if the message parameter is used.
type – is the input message type based on the MAPI mail definition. The only supported message type is CMC:IPM. A NULL value or no value defaults to CMC:IPM.
include_query – if set to true, the query or queries used in the query parameter are appended to the results set. If set to false, the query is not appended. The default is false. include_query is ignored if the message parameter is used.

Examples

Example 1 – xp_sendmail sends a text message on the backup status of an SAP ASE server to “sally” and “ramon” with a copy to the “admin” group:
```
xp_sendmail @recipient = "sally;ramon",
@subject = "Adaptive Server Backup Status",
@message = "Adaptive Server Backup for SERVER2 is complete.",
@copy_recipient="admin"
```
Example 2 – Sends “peter” the results of a query on the authors table. The results are in an attachment to the message, which consists of a file named au_lis.res, which is in the directory from which the server is being executed:
```
xp_sendmail "peter",
@query = "select * from authors",
@attachname = "au_list.res",
@attach_result= true
```

Usage

These parameters are related to the results of queries sent in a message when the query parameter is used. They are ignored if the message parameter is used instead: attachname, attach_result, echo_error, no_column_header, no_output, width, separator, dbuser, dname, include_query.

Permissions

By default, only a system administrator can execute xp_sendmail. A system administrator can grant this permission to other users.