xp_cmdshell and echo - trouble with escape

  • This works:
    SET @CommandL1 = 'echo U1 '+@ClientID+''> c:\temp\file.txt'
    exec master..xp_cmdshell @CommandL1

    The exact same command as above but written as one line fails (I get the error - Incorrect syntax near '+'):
    exec master..xp_cmdshell 'echo U1 '+@ClientID+'> c:\temp\file.txt'

    I have a lot of code similar to above, so want to economize on the lines. Besides, I would like to know how to solve this problem. Thanks for any help in advance (I tried using ^ as escape character, but no success).

    Thanks Much

    Jd

  • j mukherjee - Monday, March 20, 2017 8:20 PM

    This works:
    SET @CommandL1 = 'echo U1 '+@ClientID+''> c:\temp\file.txt'
    exec master..xp_cmdshell @CommandL1

    The exact same command as above but written as one line fails (I get the error - Incorrect syntax near '+'):
    exec master..xp_cmdshell 'echo U1 '+@ClientID+'> c:\temp\file.txt'

    I have a lot of code similar to above, so want to economize on the lines. Besides, I would like to know how to solve this problem. Thanks for any help in advance (I tried using ^ as escape character, but no success).

    Thanks Much

    Jd

    You can't do calculations on the passed parameters.  You have to do it using the first method in your post above.  To the best of my knowledge, there's no way around that.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • j mukherjee - Monday, March 20, 2017 8:20 PM

    This works:
    SET @CommandL1 = 'echo U1 '+@ClientID+''> c:\temp\file.txt'
    exec master..xp_cmdshell @CommandL1

    The exact same command as above but written as one line fails (I get the error - Incorrect syntax near '+'):
    exec master..xp_cmdshell 'echo U1 '+@ClientID+'> c:\temp\file.txt'

    I have a lot of code similar to above, so want to economize on the lines. Besides, I would like to know how to solve this problem. Thanks for any help in advance (I tried using ^ as escape character, but no success).

    Thanks Much

    Jd

    The exact code above wouldn't work for anyone and it has nothing to do with one line or not. You haven't declared the variables, haven't set one of them, have an extra quotation. When I fixed those issues, this on one line worked fine for me:

    SET @CommandL1 = 'echo U1 '+ @ClientID+' > c:\temp\file.txt' exec master..xp_cmdshell @CommandL1

    However, striving to have all this on one line is likely not the best idea. Readability and maintainability are important. If it's your own personal code not tied to any company and nothing anyone else will ever have to maintain, look at or fix then I'd guess it wouldn't matter.
    If I saw this in company code, I wouldn't be too happy and wouldn't be very impressed.

    Sue

  • Thank you for your replies. I appreciate it very much.

  • Sue_H - Tuesday, March 21, 2017 8:31 AM

    j mukherjee - Monday, March 20, 2017 8:20 PM

    This works:
    SET @CommandL1 = 'echo U1 '+@ClientID+''> c:\temp\file.txt'
    exec master..xp_cmdshell @CommandL1

    The exact same command as above but written as one line fails (I get the error - Incorrect syntax near '+'):
    exec master..xp_cmdshell 'echo U1 '+@ClientID+'> c:\temp\file.txt'

    I have a lot of code similar to above, so want to economize on the lines. Besides, I would like to know how to solve this problem. Thanks for any help in advance (I tried using ^ as escape character, but no success).

    Thanks Much

    Jd

    The exact code above wouldn't work for anyone and it has nothing to do with one line or not. You haven't declared the variables, haven't set one of them, have an extra quotation. When I fixed those issues, this on one line worked fine for me:

    SET @CommandL1 = 'echo U1 '+ @ClientID+' > c:\temp\file.txt' exec master..xp_cmdshell @CommandL1

    However, striving to have all this on one line is likely not the best idea. Readability and maintainability are important. If it's your own personal code not tied to any company and nothing anyone else will ever have to maintain, look at or fix then I'd guess it wouldn't matter.
    If I saw this in company code, I wouldn't be too happy and wouldn't be very impressed.

    Sue

    Exactly the right idea but that's not quite the 1 liner the op was looking for in their second example.  They wanted to do the concatenation within in the parameter itself without doing the concatenation in a separate variable you and they did in the OPs first example.  You cannot do it the way the OP wanted in their second example, which is what I was getting at.  🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, March 21, 2017 5:21 PM

    Sue_H - Tuesday, March 21, 2017 8:31 AM

    j mukherjee - Monday, March 20, 2017 8:20 PM

    This works:
    SET @CommandL1 = 'echo U1 '+@ClientID+''> c:\temp\file.txt'
    exec master..xp_cmdshell @CommandL1

    The exact same command as above but written as one line fails (I get the error - Incorrect syntax near '+'):
    exec master..xp_cmdshell 'echo U1 '+@ClientID+'> c:\temp\file.txt'

    I have a lot of code similar to above, so want to economize on the lines. Besides, I would like to know how to solve this problem. Thanks for any help in advance (I tried using ^ as escape character, but no success).

    Thanks Much

    Jd

    The exact code above wouldn't work for anyone and it has nothing to do with one line or not. You haven't declared the variables, haven't set one of them, have an extra quotation. When I fixed those issues, this on one line worked fine for me:

    SET @CommandL1 = 'echo U1 '+ @ClientID+' > c:\temp\file.txt' exec master..xp_cmdshell @CommandL1

    However, striving to have all this on one line is likely not the best idea. Readability and maintainability are important. If it's your own personal code not tied to any company and nothing anyone else will ever have to maintain, look at or fix then I'd guess it wouldn't matter.
    If I saw this in company code, I wouldn't be too happy and wouldn't be very impressed.

    Sue

    Exactly the right idea but that's not quite the 1 liner the op was looking for in their second example.  They wanted to do the concatenation within in the parameter itself without doing the concatenation in a separate variable you and they did in the OPs first example.  You cannot do it the way the OP wanted in their second example, which is what I was getting at.  🙂

    Yup, sorry I misunderstood what the OP was asking.

    Sue

  • Nah... don't be sorry.  You posted a great example of how it works correctly.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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