InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 12:25:53 GMT20http://www.sqlservercentral.com/Forums/Topic450843-23-1.aspxThe way I work this is:Declare @CrLf char (2)SET @CrLf = char(13) + char(10)select @cmdtxt = "echo "+ @string1_to_write + @CrLf + @string2_to_write + @CrLf" >> c:\temp\myfile.txt"I had a rough time with this at first because I tried to reverse the CRLF sequence which does NOT work. When you open your file in a "pure" text editor like NotePad, you'll have what you expect to find.ButchFri, 08 Feb 2008 22:48:34 GMTButchHhttp://www.sqlservercentral.com/Forums/Topic450843-23-1.aspxI got a much nicer way to do this.1. Setup the following table:CREATE TABLE TXTTOFILE ( LINE varchar (2000) NULL , IDCOL int IDENTITY (1, 1) NOT NULL ) 2. Then put a clustered index on IDCOL.3. Now insert each line that you want to go into the file into this table without the carriage returnsINSERT INTO TXTTOFILE SELECT @string1_to_write INSERT INTO TXTTOFILE SELECT @string2_to_write . . . . . .4. Now with a single cmdshell you can output the resultsexec xp_cmdshell 'BCP TXTTOFILE OUT D:\TXTFILE.TXT /Uuser /Ppassword /c'Now if you did it right, you should have a nice file with carriage returns and all! The key to this is the clustered index on IDCOL which keeps the file in the order which it was inserted. The BCP will just do the equilivant to a SELECT * FROM on the table, which will be in the order you inserted it.If you are still having problems doing it this way, post your code and I can go through it.Thu, 07 Feb 2008 07:12:16 GMTWill1922http://www.sqlservercentral.com/Forums/Topic450843-23-1.aspxHi Will,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:@string1_to_write VARCHAR(255),@string2_to_write VARCHAR(255),...@string6_to_write VARCHAR(255)...select @cmdtxt = "echo "+ @string1_to_write + CHAR(13) + @string2_to_write + CHAR(13) + ...+@string6_to_write " >> c:\temp\myfile.txt"exec master..xp_cmdshell @cmdtxtI 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.Thu, 07 Feb 2008 06:33:12 GMTjessica.stapehttp://www.sqlservercentral.com/Forums/Topic450843-23-1.aspxI do stuff like what you are talking about all the time. I probably have a better way to write to a txt file other than a cmd_shell echo statement. Can you post a bit more of the code and I can check it out?Wed, 06 Feb 2008 14:17:21 GMTWill1922http://www.sqlservercentral.com/Forums/Topic450843-23-1.aspxJessica,I'll second the suggestion about using a text editor which reveals hex codes. There are many available - I like using the Boxer text editor (www.boxersoftware.com) because it includes this feature and many others I've found useful as an application developer.Kind regards.CraigTue, 05 Feb 2008 07:59:10 GMTCraig-315134http://www.sqlservercentral.com/Forums/Topic450843-23-1.aspxJessica,Just as a matter of course, you should have a (text) editor that (at least) displays an opened file in hex.ChrisMon, 04 Feb 2008 20:07:49 GMTcdkellyhttp://www.sqlservercentral.com/Forums/Topic450843-23-1.aspxFigured this out myself by opening up the file in MS Word and selecting "Reveal Formatting". The code above does add a carriage return effectively.-JessicaSat, 02 Feb 2008 21:04:59 GMTjessica.stapehttp://www.sqlservercentral.com/Forums/Topic450843-23-1.aspxI am attempting to send data to a txt file using a T-SQL in a stored procedure. The file needs to contain six strings separated by carriage returns NOT newlines. I have done the following, but when I open up the file I do not see any indication that the carriage returns are actually there. I just see one long string of data.I have been using the statement below: select @cmdtxt = "echo "+ @string1_to_write + CHAR(13) + @string2_to_write + " >> c:\temp\myfile.txt"Will carriage returns in the text file be visible? Am I going about this the wrong way?Thanks,Jessica Fri, 01 Feb 2008 20:29:28 GMTjessica.stape