xp_cmdshell (Transact-SQL)

Article
12/03/2008

Spawns a Windows command shell and passes in a string for execution. Any output is returned as rows of text.

Topic link icon Transact-SQL Syntax Conventions

Syntax

xp_cmdshell { 'command_string' } [ , no_output ]

Arguments

'command_string'
Is the string that contains a command to be passed to the operating system. command_string is varchar(8000) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is required if any spaces are present in the file paths or program names referenced in command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.

no_output
Is an optional parameter, specifying that no output should be returned to the client.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Executing the following xp_cmdshell statement returns a directory listing of the current directory.

EXEC xp_cmdshell 'dir *.exe';
GO

The rows are returned in an nvarchar(255) column. If the no_output option is used, only the following will be returned:

The command(s) completed successfully.

Remarks

The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

xp_cmdshell operates synchronously. Control is not returned to the caller until the command-shell command is completed.

xp_cmdshell can be enabled and disabled by using the Surface Area Configuration tool and by executing sp_configure. For more information, see Surface Area Configuration and xp_cmdshell Option.

Important

If xp_cmdshell is executed within a batch and returns an error, the batch will fail. This is a change of behavior. In earlier versions of Microsoft SQL Server the batch would continue to execute.

xp_cmdshell Proxy Account

When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password. For example, the following command creates a proxy credential for Windows domain user SHIPPING\KobeR that has the Windows password sdfh%dkc93vcMt0.

EXEC sp_xp_cmdshell_proxy_account 'SHIPPING\KobeR','sdfh%dkc93vcMt0'

For more information, see sp_xp_cmdshell_proxy_account (Transact-SQL).

Permissions

Requires CONTROL SERVER permission.

Examples

A. Returning a list of executable files

The following example shows the xp_cmdshell extended stored procedure executing a directory command.

EXEC master..xp_cmdshell 'dir *.exe'

B. Using Windows net commands

The following example shows the use of xp_cmdshell in a stored procedure. This example notifies users by using net send that an instance of SQL Server is about to be shut down, pauses the server by using net pause, and then shuts the server down by using net stop.

CREATE PROC shutdown10
AS
    EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server 
        shutting down in 10 minutes. No more connections 
        allowed.', no_output
    EXEC xp_cmdshell 'net pause sqlserver'
    WAITFOR DELAY '00:05:00'
    EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server 
        shutting down in 5 minutes.', no_output
    WAITFOR DELAY '00:04:00'
    EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server 
        shutting down in 1 minute. Log off now.', no_output
    WAITFOR DELAY '00:01:00'
    EXEC xp_cmdshell 'net stop sqlserver', no_output

C. Returning no output

The following example uses xp_cmdshell to execute a command string without returning the output to the client.

USE master;
EXEC xp_cmdshell 'copy c:\SQLbcks\AdvWorks.bck
    \\server2\backups\SQLbcks, NO_OUTPUT';
GO

D. Using return status

In the following example, the xp_cmdshell extended stored procedure also suggests return status. The return code value is stored in the variable @result.

DECLARE @result int
EXEC @result = xp_cmdshell 'dir *.exe'
IF (@result = 0)
   PRINT 'Success'
ELSE
   PRINT 'Failure'

E. Writing variable contents to a file

The following example writes the contents of the @var variable to a file named var_out.txt in the current server directory.

DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd

F. Capturing the result of a command to a file

The following example writes the contents of the current directory to a file named dir_out.txt in the current server directory.

DECLARE @cmd sysname, @var sysname
SET @var = 'dir/p'
SET @cmd = @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd

Share via