none
Problems with xp_cmdshell

    Question

  • Hi!

    I am trying to use xp_cmdshell for the first time.

    When I run the following command in a cmd window on the server it works. (it just calls a third party tool that takes backups)

    "C:\BackupScripts\Backup.bat" "FULL" "SSDBSERV\L008" "MH" "SQL_UAT"

    "C:\BackupScripts\Backup.bat" "FULL" "SQL01" "MH" "SQL_UAT"

    Now im trying to execute it via sql using xp_cmdshell so im running:

    exec master.dbo.xp_cmdshell '"C:\BackupScripts\Backup.bat" "FULL" "SQL01" "MH" "SQLUAT"'

    but I get the error:

    The system cannot find the path specified.

    Does the xp_cmdshell command handle the quotations funny? Am I doing something wrong?

    Thanks,

    Zoe 

    Thursday, March 27, 2014 4:04 PM

Answers

  • Sorted it!

    Just had to double the quotation marks at the start and end

    exec master.dbo.xp_cmdshell '""C:\BackupScripts\Backup.bat" "FULL" "SQL01" "MH" "SQLUAT""'

    Thanks for all your help!

    Zoe

    • Marked as answer by Zoe.Ohara Friday, March 28, 2014 11:11 AM
    Friday, March 28, 2014 11:10 AM

All replies

  • Is C:\BackupScripts\Backup.bat a local path in your client machine? Is sql server installed in another machine? Also are you using an account with sysadmin priviledges?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, March 27, 2014 4:18 PM
  • Two possibilities:

    1) The batch file resides on a different machine from which SQL Server is running from.

    2) The BAT file includes a relative path, for instance ..\TEMP which does not exist from the default folder for xp_cmdshell (which is C:\Windows\Syste32).


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 27, 2014 4:20 PM
  • Its a local path on the server.  Im running it from my desktop with a sysadmin account.

    (xp_cmdshell is enabled)

    Thursday, March 27, 2014 4:20 PM
  • I've just installed SSMS on the server and ran it from there directly.

    Now I get a different error:

    'C:\BackupScripts\Backup.bat" "FULL" "SQL01" "MH" "SQL_UAT' is not recognized as an internal or external command,

    It looks like its dropping the double quotation marks at the beginning and end of the command...

    Thursday, March 27, 2014 4:33 PM
  • Ive had a trawl through the batch file and there are no relative paths mentioned.

    Thursday, March 27, 2014 4:46 PM
  • Run

    EXEC xp_cmdshell 'DIR C:\'

    Do you see the BackupScripts folder?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 27, 2014 10:49 PM
  • Yes! 

    That command returns all the folders on C: including BackupScripts

    Friday, March 28, 2014 8:22 AM
  • Sorted it!

    Just had to double the quotation marks at the start and end

    exec master.dbo.xp_cmdshell '""C:\BackupScripts\Backup.bat" "FULL" "SQL01" "MH" "SQLUAT""'

    Thanks for all your help!

    Zoe

    • Marked as answer by Zoe.Ohara Friday, March 28, 2014 11:11 AM
    Friday, March 28, 2014 11:10 AM
  • Interesting. Thanks for reporting back! Good to know if the questions comes up again in the future.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, March 29, 2014 9:47 AM