1

I am trying to export some data and fixed characters from a SQL Server table to a text file. I need a large number of such lines exported to the text file so I am trying to cut down on DECLARing and SETting a large number of command variables by putting them in one line (it will reduce the code size enormously). Here is what I am trying to do:

This works fine:

DECLARE @ClientID varchar(50)
SET @ClientID = (select ClientID from inserted)

DECLARE @CommandL1 varchar(512)
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 (without DECLARE and SET, preferred way to go to reduce code) fails (I get the error - Incorrect syntax near (first) '+'):

exec master..xp_cmdshell 'echo U1 '+@ClientID+'> c:\temp\file.txt' --ERROR

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). How does one change the second line (shown above with --ERROR) so it works properly?

Jd

S3S
  • 24,809
  • 5
  • 26
  • 45
Jay
  • 13
  • 5
  • what's the ClientID set to? Also, I think you have an extra ' in your first statement, the setting of @CommandL1 – S3S Mar 21 '17 at 18:20
  • I get ClientID as SET @ClientID = (select ClientID from inserted); this part works fine and after table inserts/updates, correct ClientID is exported to the text file, using the first method. You were absolutely right about extra ' - I have edited it now. Thanks for your reply. – Jay Mar 21 '17 at 18:34
  • So `ClientID` is an `INTEGER` or is it a `VARCHAR`? Are you sure that only 1 row (record / value) is returned in your sub-select `(select ClientID from inserted)`? – S3S Mar 21 '17 at 18:36
  • I am declaring it as a varchar - DECLARE @ClientID varchar(50) - yes, only one record is returned. Since the first set of 3 lines is working, I am thinking the problem is how xp_cmdshell is interpreting the second one-line code - it says "Incorrect Syntax near +" when I run it in a SQL query window. – Jay Mar 21 '17 at 18:42
  • Based on the SSIS examples of xp_cmdshell, it looks like it simply does not accept dynamically generated strings. You might try placing it within a CAST or CONVERT statement to see if you can work around this behavior - `exec xp_cmdshell CONVERT(varchar(max), 'echo U1 '+@ClientID+'> c:\temp\file.txt')` – Laughing Vergil Mar 21 '17 at 18:51

1 Answers1

0

According to MSDN, you are going to have to keep that statement in Dynamic SQL so that the command shell doesn't read 

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

as

echo U1 + @ClientID + '> c:\temp\file.txt'

xp_cmdshell takes a string parameter, so you need to build the string explicitly first as you have done in the beginning. The parameter is terminated after the ending quote before the +, therefor the + is a syntax error since xp_cmdshell doesn't expect anything after the string parameter other than, NO_OUTPUT as an optional parameter. So it interprets your + as an incorrect parameter.

S3S
  • 24,809
  • 5
  • 26
  • 45
  • Thank you very much. More work, but at least it will do the job. I appreciate your help. – Jay Mar 21 '17 at 18:52
  • No problem. I added a bit more about the parameter termination. That last ' in SQL will read as the end of the string parameter. – S3S Mar 21 '17 at 18:53