I am creating a stored procedure that will query a database for some information and then generate a txt file with that information. This is for a medical application, so I have to make sure to follow HL7 guidelines. One of the guidelines specifies that each line must be separated by carriage return, not newline.
In the end, I need six lines of data separated by carriage returns. So I am saving each line to a variable and then using the echo command at the end of the procedure like below:
select @cmdtxt = "echo "+ @string1_to_write + CHAR(13) + @string2_to_write + CHAR(13) + ...+@string6_to_write " >> c:\temp\myfile.txt"
exec master..xp_cmdshell @cmdtxt
I thought this was working right, but actually the carriage returns are not being generated. I am not an expert at by any means at T-SQL, but I used this method because it was simple. If you know of a better way, please let me know.
I found one site that said you can't use CHAR(13) with select statements, but I have seen a lot of code posted to the contrary.