SQL Server Performance

SQL - Strings with spaces -

Discussion in 'General DBA Questions' started by gkrishn, Aug 5, 2005.

  1. gkrishn New Member

    Hi i am tryin with the following query and it throws error ,<br /><br />master..xp_cmdshell 'dir C:program'+' '+'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=':p' />]<br /><br />rajiv<br />NewBie-23
  2. Madhivanan Moderator

    Try this


    Declare @s varchar(100)
    set @s='''Dir C:program'+' '+'Files'''

    Exec master..xp_cmdshell @s



    Madhivanan

    Failing to plan is Planning to fail
  3. gkrishn New Member

    print (@s) shows

    'Dir C:program 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
  4. Madhivanan Moderator

    Remove after Files


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


    Madhivanan

    Failing to plan is Planning to fail
  5. gkrishn New Member

    Again it throws error,
    file not found, now its actually lookin in C: drive not c:program 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
  6. Adriaan New Member

    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 "C:program Files"

    In your script, this becomes:

    set @s='DIR "C:program Files"'

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

    Make sure this gives you the correct result

    Exec master..xp_cmdshell 'Dir C:program files'

    If so, so as that code


    Madhivanan

    Failing to plan is Planning to fail
  8. Madhivanan Moderator

    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
  9. gkrishn New Member

    Madhivanan/Adriaan ,

    Thanks guys it works !!

    rajiv
    NewBie-23
  10. Adriaan New Member

    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 "C:program Files"'
  11. Madhivanan Moderator

    >>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:program Files"'

    Thats absolutely correct


    Madhivanan

    Failing to plan is Planning to fail
  12. Adriaan New Member

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

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

    Declare @s varchar(100)
    set @s='Dir "C:program'+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
  14. Adriaan New Member

    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:program'+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?
  15. ghemant Moderator

    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
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
  16. Adriaan New Member

    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!

Share This Page