Adding CRLF to T Sql

Question

Post reply

Adding CRLF to T Sql

vic manfredi

SSC Veteran

Points: 269
More actions
December 22, 2005 at 7:42 am

#152678

Can any one tell me why this is not working
CREATE TRIGGER trgORDERS_INSERT ON [Orders]
FOR INSERT
As
DECLARE @sSQL VARCHAR(8000)
DECLARE @sOutFolder VARCHAR(255)
DECLARE @CRLF char(2)
set @CRLF = char(10) + char(13)
--specify the data exchange folder here
select @sOutFolder='c:\xml\orders\'
select @sSQL = 'echo ^<ORDERS^>'+@CRLF+'^<ORDERS^> >'+ @sOutFolder + 'Orders_'+convert(varchar(255),BuyerOrderNumber)+'.xml' from inserted
exec master..xp_cmdshell @sSQL

When I remove the @CRLF variable it works fine. But I need to add it to the file

thanks
Vic

Viewing 5 posts - 1 through 4 (of 4 total)

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

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

Not sure if this is the cause but CRLF is x0D0A Char(13) + Char(10)

vadba SSChampion Points: 11133 More actions · Answer 2

What are you trying to accomplish? As Allen mentioned you've reversed the Cr and LF. However, even when that is corrected, the output you are trying to run with xp_cmdshell is:

echo ^<ORDERS^>

^<ORDERS^> >c:\xml\orders\Orders_401.xml

Are you trying to redirect the output of a query to the file c:\xml\orders\Orders_401.xml ?

If so, you can only process one line at a time with xp_cmdshell (which runs cmd.exe). Consider running the SQL query with osql.exe using the -o switch, or creating a batch file (.cmd) you can call. Anoher option is to store the BuyerOrderNumber in a table, and process that table periodically with a scheduled job.

To create a file with three lines from a command prompt, you'd need to run three commands:

echo ^<ORDERS^> >c:\xml\orders\Orders_401.xml

echo 12345 >>c:\xml\orders\Orders_401.xml

echo ^</ORDERS^> >>c:\xml\orders\Orders_401.xml

to produce

12345

</ORDERS>

vic manfredi SSC Veteran Points: 269 More actions · Answer 3

thanks for the help. You answered my question why it was not working. xp_cmdshell only does one line at a time I did not know that. I went and wrote an active x script that outputs the file. Everytime an order is added to the orders table I create an xml file for import into my legacy system. If anyone wants the code let me know I will be happy to share.

thanks and have a great holiday everyone

Peter E. Kierstead SSCarpal Tunnel Points: 4114 More actions · Answer 4

Try:

Exec sp_CmdShell 'Echo Text for Line 1 & Echo Text for Line 2'

The "&" is the Command-Shell command separator. Of course, you could redirect this output to a file as such:

Exec sp_CmdShell 'Echo Text for Line 1 > MyFile.txt & Echo Text for Line 2 >> MyFile.txt'

PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.