xp_readmail  xp_startmail

Chapter 3: System Extended Stored Procedures

xp_sendmail

Description

Windows NT 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 email 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. If not used, defaults to “Sybase SQL Server Message”.

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 Adaptive Server messages, including the count of rows affected message, along with the query results. If set to false, does not send Adaptive 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 Adaptive 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

Permissions

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





Copyright © 2005. Sybase Inc. All rights reserved. xp_startmail

View this book as PDF