' 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.

0 (success) or 1 (failure)

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.

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 Policy-Based Management or by executing sp_configure. For more information, see Understanding 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

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

Requires CONTROL SERVER permission.

A. Returning a list of executable files

EXEC master..xp_cmdshell 'dir *.exe'

B. Using Windows net commands

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

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

D. Using return status

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

E. Writing variable contents to a file

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

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

Reference

Other Resources

Help and Information