xp_cmdshell and echo - trouble with escape

Question

xp_cmdshell and echo - trouble with escape

j mukherjee

SSC-Addicted

Points: 420
More actions
March 20, 2017 at 8:20 pm

#328445

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

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

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

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)

Sue_H SSC Guru Points: 90860 More actions · Answer 2

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

j mukherjee SSC-Addicted Points: 420 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

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)

Sue_H SSC Guru Points: 90860 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 6

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)