T-Sql calling an .exe is working but problem with space in the path

Question

T-Sql calling an .exe is working but problem with space in the path

jellybean

SSCrazy

Points: 2014
More actions
July 17, 2015 at 6:03 am

#305882

H everyone,
I'm testing calling an .exe from T-Sql. Here is the code (whic his working btw:
DECLARE @sqlCmd varchar(300)
set @SqlCmd = 'powershell.exe Robocopy "C:\testSource" "c:\testDestination" *.bak /S'
EXEC xp_cmdshell @sqlCmd
Problem is though - if there is a space in the path name like say "C:\test Destination" - it throws an error like incorrect wrong parameter ie it thinks its a parameter for robocopy!
Any assistance/help here to make it work much appreciated,
J.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think the issue is xp_cmshell;

as i remember it, with xp_cmdshell you are limited to a single pair of double quotes to a command, so ONE of your two paths can be quoted with double quotes, and the other one really has to not have spaces in it.

if this is a SQL job step, you can use a command line or run robocopy via a powershell command with dbl quotes everywhere no problem, but if you are doing it via xp_cmdshell, you need to guarantee one of your paths, either source or destination, has no spaces.

DECLARE @sqlCmd varchar(300)

set @SqlCmd = 'powershell.exe Robocopy C:\testSource "c:\test folder\ with spaces\final Destination" *.bak /S'

EXEC xp_cmdshell @sqlCmd

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jellybean SSCrazy Points: 2014 More actions · Answer 2

Hi, thanks for that. Actually I found that this works a treat for me in my env:

set @SqlCmd = 'powershell.exe Robocopy C:\share\testSource \"c:\share\test Destination\" *.bak /S'

Cheers & thanks,

J.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

curious why you're calling powershell in a cmd shell to then turn around and call robocopy. why not this?

DECLARE @sqlCmd varchar(300)

set @SqlCmd = 'Robocopy C:\testSource "c:\test folder\ with spaces\final Destination" *.bak /S'

EXEC xp_cmdshell @sqlCmd

I've seen odd return codes from robocopy so I call it from powershell to be able trap those before returning 0 to the caller but you're not showing that you use the return code.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

jellybean SSCrazy Points: 2014 More actions · Answer 4

Thank you Orlando - probably ignorance on my part (to answer your question).

I'm calling this from inside a maintence plan package. I will refactor and see if it works.

Thanks for the correction etc.

J.