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.
Examplesxp_cmdshell 'dir *.jpg'
Executing this xp_cmdshell statement returns the following result set:
xp_cmdshell 'dir *.exe', NO_OUTPUTHere is the result:
The command(s) completed successfully.<!–[if gte vml 1]> <![endif]–><!–[if !vml]–><!–[endif]–>
Examples
Copy FileEXEC 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 intEXEC @result = xp_cmdshell 'dir *.exe'IF (@result = 0)PRINT 'Success'ELSEPRINT '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
