Home

	Expand Search

Reference Manual

Chapter 9: System Extended Stored Procedures

Chapter 9

This chapter describes the system extended stored procedures (ESPs), which are extended stored procedures supplied by Sybase.

Table 9-1 lists the system extended stored procedures discussed in this chapter.

Table 9-1: System extended stored procedures

Procedure	Description	Platform
xp_cmdshell	Executes a native operating system command on the host system running Adaptive Server.	All Supporting DLLs
xp_deletemail	Deletes a message from the Adaptive Server message inbox.	NT Only
xp_enumgroups	Displays groups for a specific Windows NT domain.	NT Only
xp_findnextmsg	Retrieves the message identifier of the next message in the Adaptive Server message inbox.	NT Only
xp_logevent	Provides for logging a user-defined event in the Windows NT Event Log.	NT Only
xp_readmail	Reads a message from the Adaptive Server message inbox.	NT Only
xp_sendmail	Sends a message to the specified recipients using the MAPI interface.	NT Only
xp_startmail	Starts an Adaptive Server mail session.	NT Only
xp_stopmail	Stops an Adaptive Server mail session.	NT Only

Introduction

The system extended stored procedures, created by installmaster at installation, are located in the sybsystemprocs database and are owned by the System Administrator. They can be run from any database.

Permissions on System ESPs

Since system extended stored procedures are located in the sybsystemprocs database, their permissions are also set there.

Users with the sa_role have default execution permissions on the system ESPs. These System Administrators can grant execution permissions to other users.

DLLs associated with System ESPs

You can get the names of the DLLs associated with the system ESPs by running sp_helpextendedproc in the sybsystemprocs database.

Using System ESPs

The system ESPs follow the same calling conventions as the regular system procedures.

The only additional requirement for system ESPs is that the Open Server application, XP Server, must be running. Adaptive Server starts XP Server the first time an ESP is invoked. XP Server continues to run until you shut down Adaptive Server.

Manual Pages for System Extended Stored Procedures

xp_cmdshell

Function

Executes a native operating system command on the host system running Adaptive Server.

Syntax

xp_cmdshell command [, no_output]

Parameters

command - is the operating system command string; maximum length is 255 bytes.

no_output - if specified, suppresses any output from the command.

Examples

1. xp_cmdshell 'copy C:\log A:\log.0102', no_output

   Silently copies the file named log on the C drive to a file named log.0102 on the A drive.

2. xp_cmdshell 'date'

   Executes the operating system's date command and returns the current date as a row of data.

Comments

• xp_cmdshell returns any output, including operating system errors, as rows of text in a single column.
• xp_cmdshell is run from the current directory of the XP Server.
• The width of the column of returned output is 80 characters. The output is not formatted.
• xp_cmdshell cannot perform commands that require interaction with the user, such as "login".
• The user context in which an operating system command is executed via xp_cmdshell is controlled by the value of the xp_cmdshell context configuration parameter. If this parameter is set to 1 (the default), xp_cmdshell restricts permission to users with System Administration privileges at the operating system level. If this parameter is set to 0, xp_cmdshell uses the security context of the operating system account under which Adaptive Server is running. Therefore, using xp_cmdshell with the xp_cmdshell context configuration parameter set to 0, any user can execute operating system commands using the permissions of the account running Adaptive Server. This account may have fewer restrictions than the user's own account.

  For more information about t xp_cmdshell context, see the System Administration Guide

• Regardless of the value of xp_cmdshell context, if the user who is executing xp_cmdshell is not a System Administrator (does not have the sa_role), a System Administrator must have granted that user explicit permission to execute xp_cmdshell. For example, the following statement grants "joe" permission to execute xp_cmdshell:

  grant execute on xp_cmdshell to joe

Permissions

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

See Also

System procedures

sp_configure

xp_deletemail

(Windows NT only)

Function

Deletes a message from the Adaptive Server message inbox.

Syntax

xp_deletemail [msg_id]

Parameters

msg_id - is the message identifier of the mail message to be deleted.

Examples

1. declare @cur_msg_id binary(255)
   xp_deletemail @msg_id = @cur_msg_id

   Deletes from the Adaptive Server message inbox the message with the message identifier specified in the cur_msg_id variable.

2. xp_deletemail

   Deletes the first message from the Adaptive Server message inbox.

Comments

• Obtain the msg_id using xp_findnextmsg.
• If the msg_id parameter is not used, the message to be deleted defaults to the first message in the message inbox.

Permissions

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

See Also

System ESPs	xp_findnextmsg, xp_startmail
System procedures	sp_processmail

xp_enumgroups

(Windows NT only)

Function

Displays groups for a specified Windows NT domain.

Syntax

xp_enumgroups [domain_name]

Parameters

domain_name - is the Windows NT domain for which you are listing user groups.

Examples

1. xp_enumgroups

   Lists all user groups on the Windows NT computer running XP Server.

2. xp_enumgroups 'PCS'

   Lists all user groups in the PCS domain.

Comments

• xp_enumgroups displays all local user groups if no parameter is passed.
• A domain is a named collection of computers that share a common user account database and security policy.
• A return status of 0 indicates success; 1 indicates failure.

Permissions

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

xp_findnextmsg

(Windows NT only)

Function

Retrieves the next message identifier from the Adaptive Server message inbox.

Syntax

xp_findnextmsg @msg_id = @msg_id output [, type]
[, unread_only = {true | false}]

Parameters

msg_id - on input, specifies the message identifier that immediately precedes the one you are trying to retrieve. Places the retrieved message identifier in the msg_id output parameter, which must be of type binary.

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.

unread_only - if this parameter is set to true, xp_findnextmsg considers only unread messages. If this parameter is set to false, xp_findnextmsg considers all messages, both read and unread, when retrieving the next message identifier. The default is true.

Examples

1. xp_findnextmsg @msg_id = @out_msg_id output

   Returns, in the @out_msg_id output variable, the message identifier of the next unread message after the message specified by the @out_msg_id.

2. xp_findnextmsg @msg_id = @out_msg_id output, NULL, @unread_only = false

   Returns, in the @out_msg_id output variable, the message identifier of the next message after the message specified by the @out_msg_id. The message may be read or unread.

Comments

• When xp_findnextmsg can find no more messages in the inbox, it returns a status of 1.
• xp_deletemail and xp_readmail use the message identifier returned by xp_findnextmsg.

Permissions

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

See Also

System ESPs	xp_deletemail, xp_readmail, xp_startmail
System procedures	sp_processmail

xp_logevent

(Windows NT only)

Function

Provides for logging a user-defined event in the Windows NT Event Log from within Adaptive Server.

Syntax

xp_logevent error_number, message [, type]

Parameters

error_number - is the user-assigned error number. It must be equal to or greater than 50000.

message - is the text of the message that is displayed in the description field of the event viewer. The maximum length of the message is 255 bytes. Enclose the message in quotes.

type - describes the urgency of the event. Values are informational, warning, and error. The default is informational. Enclose the value in quotes.

Examples

1. xp_logevent 55555, 'Email message deleted.'

   An informational event, number 55555, will be logged in the Windows NT Event Log. The text of the description in the event detail window is "Email message deleted".

2. xp_logevent 66666, 'DLL not found.', 'error'

   An error event, number 66666, will be logged in the Windows NT Event Log. The text of the description in the event detail window is "DLL not found".

Comments

• The following table describes the default event details for events generated with xp_logevent:

  Detail	Value
  User	N/A
  Computer	Name of machine running XP Server
  Event ID	12
  Source	Name of Adaptive Server
  Category	User

Permissions

Only a System Administrator can execute xp_logevent.

xp_readmail

(Windows NT only)

Function

Reads a message from the Adaptive Server message inbox.

Syntax

xp_readmail [msg_id]
[, recipients output]
[, sender output]
[, date_received output]
[, subject output]
[, cc output]
[, message output]
[, attachments output]
[, suppress_attach = {true | false}]
[, peek = {true | false}]
[, unread = {true | false}]
[, msg_length output]
[, bytes_to_skip [output]]
[, type [output]]

Parameters

msg _id - specifies the message identifier of the message to be read by xp_readmail. If the msg_id parameter is not used, the message defaults to the first unread message in the message box, if unread is true, or to the first message in the message box, if unread is false.

recipients - is a semicolon-separated list of the recipients of the message.

sender - is the originator of the message.

date_received - is the date the message was received.

subject - is the subject header of the message.

cc - is a list of the message's copied (cc'd) recipients (separated by semicolons).

message - is the text of the message body. If the length of the message body, obtained from the msg_length output parameter, is greater than 255, use the byte_to_skip and msg_length parameters to read the message in 255-byte increments.

attachments - is a list of the temporary paths of the attachments (separated by semicolons). attachments is ignored if suppress_attach is true.

suppress_attach - if set to true, prevents the creation of temporary files for attachments. The default is true.

peek- if set to false, flags the message as unread after it has been read. If set to true, flags the message as an unread message, even after it has been read. The default is false.

unread_only - if set to true, xp_readmail considers only unread messages. If set to false, xp_readmail considers all messages, whether they are flagged as read or unread. The default is true.

msg_length - is the total length of the message, in bytes. Used with the bytes_to_skip parameter, allows xp_readmail to read messages in 255-byte increments.

bytes_to_skip - on input, if not 0, specifies the number of bytes to skip before reading the next 255 bytes of the message into the message output parameter. On output, contains the offset in the message (the previous value of bytes_to_skip plus the msg_length that is output with the call) from which to start reading the next 255-byte increment.

type - is the 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.

Examples

1. declare @msgid binary(255)
   declare @originator varchar(20)
   declare @mess varchar(255)
   exec xp_findnextmsg @msg_id = @msgid output
   exec xp_readmail @msg_id = @msgid,
   @sender = @originator output,
   @message = @mess output

   xp_readmail reads the first unread message in the message inbox. It gets the message identifier for this message from the @msgid variable, where it has been stored by the xp_findnextmsg ESP. xp_readmail stores the sender's name in the @originator variable and the message body in the @mess variable.

2. declare @msgid binary(255)
   declare @mess varchar(255)
   declare @len int
   declare @skip int = 0
   exec xp_findnextmsg @msgid output
   exec xp_readmail @msg_id = @msgid,
   @message = @mess output
   @msg_length = @len output,
   @bytes_to_skip = @skip output
   print @mess
   if (@len > 255)
   begin
   while (@skip < @len)
   begin
   xp_readmail @msg_id = @msgid,
   @message = @mess output,
   @bytes_to skip = @skip output
   print @mess
   end
   end

   Reads the first 255 bytes of the message for which the message identifier is output by xp_findnextmsg. If the total length of the message exceeds 255 bytes, reads the next 255 bytes and continues until there are no more bytes to read.

Comments

• xp_readmail reads a message from the Adaptive Server message inbox.
• To get the message identifier of the next message in the message inbox, use xp_findnextmsg.

Permissions

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

See Also

System ESPs	xp_deletemail, xp_findnextmsg, xp_sendmail, xp_startmail
System procedures	sp_processmail

xp_sendmail

(Windows NT only)

Function

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}]
[, 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.

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

1. xp_sendmail @recipient = "sally";"ramon",
   @subject = "Adaptive Server Backup Status",
   @message = "Adaptive Server Backup for SERVER2 is complete.",
   @copy_recipient="admin"

   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.

2. xp_sendmail "peter",
   @query = "select * from authors",
   @attachname = "au_list.res",
   @attach_result= true

   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.

Comments

• The following 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.

See Also

System ESPs	xp_deletemail, xp_findnextmsg, xp_readmail, xp_startmail
System procedures	sp_processmail
Utility	isql

xp_startmail

(Windows NT only)

Function

Starts an Adaptive Server mail session.

Syntax

xp_startmail [mail_user] [, mail_password]

Parameters

mail_user - is a mail profile name used by Adaptive Server to log into the Windows NT mail system. If mail_user is not used, xp_startmail uses the mail user name that was used to set up Sybmail's Adaptive Server account.

mail_password - is the mail password used by Adaptive Server to log into the Windows NT mail system. If mail_password is not used, xp_startmail uses the mail password that was used to set up Sybmail's Adaptive Server account.

Examples

1. xp_startmail

   Starts an Adaptive Server mail session using the mail user name and password for Sybmail's user account.

2. xp_startmail "mailuser", "tre55uu"

   Starts an Adaptive Server mail session with "mailuser" as the profile name and the password associated with that profile name.

Comments

• xp_startmail will not start an Adaptive Server mail session if one is already running.
• An Adaptive Server mail session must be started, either by an explicit call to xp_startmail or by configuring Adaptive Server to start an Adaptive Server mail session automatically at start-up, before any Sybmail-related system ESPs or the sp_processmail stored procedure can be executed. For information about initiating an Adaptive Server mail session automatically at start-up, see start mail session in the System Administration Guide.
• When the Windows NT automail session is not on, you must use the mail_user and mail_password parameters with xp_startmail.
• To see the default mail_user value from the fullname field for the "sybmail" user account, use the sp_displaylogin system procedure as follows:

      sp_displaylogin sybmail

Permissions

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

See Also

System ESPs	xp_stopmail
System procedures	sp_configure

xp_stopmail

(Windows NT only)

Function

Stops an Adaptive Server mail session.

Syntax

xp_stopmail

Parameters

None.

Examples

1. xp_stopmail

   Stops an Adaptive Server mail session.

Comments

• Sybmail-related system ESPs and the sp_processmail stored procedure cannot be executed after an Adaptive Server mail session has been terminated with xp_stopmail.

Permissions

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

See Also

System ESPs	xp_startmail
System procedures	sp_processmail