Tag Archives: execute batch file with parameter

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