Chapter 3: System Extended Stored Procedures

xp_cmdshell

Description

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

Syntax

xp_cmdshell command [, no_output ] [ return_status | no_wait ]

Parameters

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

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

return_status: if specified, returns the completion status of the operating system command specified in the command parameter. If you do not use this parameter, the returned value is either 0 for success, or or 1 for failure, respectively.

no_wait: if specified, the xp_cmdshell operation immediately returns to the caller and the specified command executes as a background process. You see no output, and the returned result reflects only the success or failure of starting the command as a background process, not the success or failure of the process itself.

Examples

Example 1

On Windows – Silently copies the file named log on the C drive to a file named log.0102 on the A drive:

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

Example 2

On UNIX – Executes the operating system’s ls command and returns the list directory contents as a row of data:

xp_cmdshell 'ls'

Usage

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.
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
```
To find out if xp_cmdshell was successful in spawning an external command XP Server, enter the following, where command is the name of the command you ran with xp_cmdshell:
```
@ret = exec xp_cmdshell command
```
If xp_cmdshell was successful, @ret = exec xp_cmdshell command returns a value of 0. If xp_cmdshell failed, @ret = exec xp_cmdshell command returns a value of 1.
To find out if the command you ran using xp_cmdshell was itself successful, enter the following, where command is the name of the command you ran with xp_cmdshell:
```
@ret = exec xp_cmdshell command, return_status
```
@ret = exec xp_cmdshell command, return_status causes xp_cmdshell to return the actual exit status code of the command. If a failure occurrs and XP Server cannot run the command, xp_cmdshell returns a value of 1. If the command runs successfully, xp_cmdshell returns a value of 0.

If the command was successful, @ret = exec xp_cmdshell command returns a value of 0. If the command failed, @ret = exec xp_cmdshell command returns a value of 1.

Both @ret = exec xp_cmdshell command and @ret = exec xp_cmdshell command, return_status are backward-compatible. Old stored procedures that do not use the return_status parameter treat @ret = exec xp_cmdshell command, return_status as if it were @ret = exec xp_cmdshell command.

Also, the no_output parameter can still be used in combination with return_status, in any order.
You must use the cmdstr column name when you create a proxy table with the xp_cmdshell remote procedure:
```
create existing table xpoutput
(
       cmdstr varchar(255) null
)
external procedure at "THIS...xp_cmdshell"

select cmdstr from xpoutput where cmdstr = "date"
```
If you do not use cmdstr, you see an error message.

See the section, “Remote procedures as proxy tables” in the Component Integration Services User’s Guide for more information about results returned from the proxy table.

Permissions

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