SQL Server Performance Forum – Threads Archive

SQL – Strings with spaces –

Hi i am tryin with the following query and it throws error , master..xp_cmdshell ‘dir C

rogram’+’ ‘+’Files' sysntax error master..xp_cmdshell ‘dir C:[Program Files]' path not found 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=’

‘ />] rajiv NewBie-23

Try this
Declare @s varchar(100)
set @s=”’Dir C

rogram’+’ ‘+’Files”’ Exec master..xp_cmdshell @s Madhivanan Failing to plan is Planning to fail

print (@s) shows ‘Dir C

rogram 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

Remove after Files
Declare @s varchar(100)
set @s=’Dir C

rogram’+’ ‘+’Files’
Exec master..xp_cmdshell @s
Madhivanan Failing to plan is Planning to fail

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

rogram 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 "C

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

Make sure this gives you the correct result Exec master..xp_cmdshell ‘Dir C

rogram files’ If so, so as that code
Madhivanan Failing to plan is Planning to fail

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/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"’

>>Why are you treating blank spaces in strings in a special way? Yes
I think He just wanted for an example >>set @s=’Dir "C

rogram Files"’ Thats absolutely correct
Madhivanan Failing to plan is Planning to fail

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

hi,
this can be achieved using space() also — as you are looking for blank spaces Declare @s varchar(100)
set @s=’Dir "C

rogram’+space(1)+’Files"’
Exec xp_cmdshell @s
print @s
HTH
regards hsGoswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

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 "C

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

Hi,

quote:It can be achieved that way, sure. But what’s the point?

dear sir,
i m just giving my opinion about space() function as originator is trying to do some tweak using space ….. i appologizes if you are hurted but my intention was only to draw attention of originator about space().
[:I] hsGoswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

No one’s hurt, really.[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] The original question showed signs of confusion, so let’s not add to that. Nevertheless, keep the opinions coming!

]]>