XP_cmdshell extended stored procedure (Execute Winows commands)


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.


xp_cmdshell {command_string} [, no_output]



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.


Is an optional parameter executing the given command_string, and does not return any output to the client.

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]–>

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

2 thoughts on “XP_cmdshell extended stored procedure (Execute Winows commands)

Leave a Reply to Nilesh Sonawale Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s