SQLTeam.com | Weblogs | Forums

Issue writing to file


#1

I have been trying to write a record to a text file but an getting an error
SET @WriteLine = 'echo ' + @OASORTCODE
+ @OAACCOUNTNUMBER
+ @TCode
+ @OASORTCODE
+ @OAACCOUNTNUMBER
+ SPACE(4)
+ @VALUE
+ 'LIC'
+ @Licence
+ 'SERIAL'
+@SerialNumber
+ 'CONTRA'
+ SPACE(12)
+ LEFT(UPPER(@OACCOUNTNAME) + SPACE(18), 18)
+ SPACE(1)
+ dbo.func_PayDate_To_ProcessingDate (@PayDate) + '>> ' + @FileName

Select @WriteLine returns
echo 1234561234567801712345612345678 00003631252XXXXXXXXXSERIALXXXCONTRA COMPANYS & CO (EXC 66666>> \xxx\xxxx\xx.txt
which is correct, however when I fire this command EXEC @xpreurn_Value = master..xp_cmdshell @WriteLine
I get the following

Return = 1

output
1234561234567801712345612345678 00003631252XXXXXXXXXSERIALXXXCONTRA COMPANYS
'CO' is not recognized as an internal or external command,
operable program or batch file.


#2

The ampersand (&) is a reserved character in windows command shell. So you need to escape it. The escape character is caret (^). So change your query so it writes out something like this:

echo 1234561234567801712345612345678 00003631252XXXXXXXXXSERIALXXXCONTRA COMPANYS ^& CO

You may also want to look up other reserved characters and escape them as well.