SQL - Strings with spaces -

Hi i am tryin with the following query and it throws error ,<br /><br />master..xp_cmdshell 'dir Crogram'+' '+'Files'<br />sysntax error<br />master..xp_cmdshell 'dir C:[Program Files]'<br />path not found<br /><br /><br />Please help. Not sure whether its foolishness,thsi could be a minor error but i couldnt findout [<img src='/community/emoticons/emotion-4.gif' alt='' />]<br /><br />rajiv<br />NewBie-23

print (@s) shows

'Dir Crogram Files'

but while executing it gives error ...

''Dir' is not recognized as an internal or external command,
operable program or batch file.
NULL


?? any idea

rajiv
NewBie-23

Again it throws error,
file not found, now its actually lookin in C: drive not crogram files .

if i give c: it works fine. Can you execute this in your local machine and paste me the query.

Thanks Madhivanan for your help



rajiv
NewBie-23

That's not the proper formatting for long file names on a command line.

If a path on the command line does not comply to the 8.3 format, you delimit the path with double-quote characters:
DIR "Crogram Files"

In your script, this becomes:

set @s='DIR "Crogram Files"'

As long as you're not using a compatibility level setting for double-quotes being used around column names, this should work.

Madhivanan/Adriaan ,

Thanks guys it works !!

rajiv
NewBie-23

quote:Originally posted by Madhivanan

Thanks Adriaan
The new code is

Declare @s varchar(100)
set @s='Dir C:"Program'+' '+'Files"'
Exec master..xp_cmdshell @s


Madhivanan

Failing to plan is Planning to fail

Madhivanan,

Why are you treating blank spaces in strings in a special way?

Trust me,
set @s='Dir C:"Program'+' '+'Files"'

is just a difficult way of writing
set @s='Dir C:"Program Files"'


EDIT:
Actually you're putting the double-quote characters in the wrong places. The whole path must be between the double-quotes, not only the part of it that is not in the 8.3 format.

My last example is incorrect, it shoud read:
set @s='Dir "Crogram Files"'

Sorry, yes now I see you were just elaborating on Rajiv's code.

quote:Originally posted by ghemant

hi,
this can be achieved using space() also -- as you are looking for blank spaces

Declare @s varchar(100)
set @s='Dir "Crogram'+space(1)+'Files"'
Exec xp_cmdshell @s
print @s


HTH
regards

hsGoswami
ghemant@gmail.com
"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami

It can be achieved that way, sure. But what's the point?

No one's hurt, really.[<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><br />The original question showed signs of confusion, so let's not add to that. Nevertheless, keep the opinions coming!