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
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.