xp_cmdshell

Executes a given command string or batch file as an operating-system command shell and returns any output as rows of text.

Permission/Rights: Only SysAdmin fixed role can execute it.

Syntax

xp_cmdshell {‘command_string‘} [, no_output]

Arguments

‘command_string‘

Is the command string to execute at the operating-system command shell or from DOS prompt. command_string is varchar(255) 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 necessary if any spaces are present in the file paths or program names referenced by 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 executing the given command_string, and does not return any output to the client.

Examples
xp_cmdshell 'dir *.jpg'

Executing this xp_cmdshell statement returns the following result set:

xp_cmdshell 'dir *.exe', NO_OUTPUT

Here is the result:

The command(s) completed successfully.

<!–[if gte vml 1]&gt; &lt;![endif]–><!–[if !vml]–><!–[endif]–>

Examples

Copy File
EXEC xp_cmdshell 'copy c:\sqldumps\jshah143.bak \\server2\backups\jshah143.bak',  NO_OUTPUT

 

Use return status

In this 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'

 

Pass the parameter to batch file

DECLARE @sourcepath VARCHAR(100)
DECLARE @destinationpath VARCHAR(1000)
SET @sourcepath = ' c:\sqldumps\jshah143.bak '
SET @destinationpath = '\\server2\backups\jshah143.bak'
 
SET @CMDSQL = 'c:copyfile.bat' + @sourcepath + @destinationpath
EXEC master..XP_CMDShell @CMDSQL