New line char in file using SQL

Question

I'm making some test variable and I insert a new line character into it. When I print it everything is ok, 1 word is below the second one. But when I write the value of this variable into *.txt file the new line character doesn't work. Here is my example:

DECLARE @cmd varchar(200), @var varchar(200)
SET @var = 'Hello' + CHAR(13) + 'world'

SET @cmd = 'echo ' + @var + ' > E:\s.txt'
print @cmd
EXEC master..xp_cmdshell @cmd

I was trying with DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10) or \r\n instead of this CHAR(13) but no effect :/ Could someone give me small hint about this problem? I'm using Microsoft SQL Server 2008 R2

You should probably get the syntax working [from a batch file](http://stackoverflow.com/questions/132799/how-can-you-echo-a-newline-in-batch-files) first then pass the working syntax. — Martin Smith, Dec 27 '14 at 18:39
Try using another utility instead of echo. I reckon echo only read up to the newline char — Tien Dinh, Dec 27 '14 at 19:30
normally I got simple HeeloWorld, but using solution below I don't get anything, file isn't even created — user3762819, Dec 27 '14 at 19:57

Gordon Linoff · Answer 1 · 2014-12-27T16:26:15.810

0

Does this work?

DECLARE @cmd varchar(200), @var varchar(200);
SET @var = 'Hello
world';

SET @cmd = 'echo ' + @var + ' > E:\s.txt';
print @cmd;
EXEC master..xp_cmdshell @cmd;

EDIT:

I believe your problem is escaping the new line in the windows shell. Try the escape character '^':

DECLARE @cmd varchar(200), @var varchar(200);
SET @var = 'Hello^
world';

SET @cmd = 'echo ' + @var + ' > E:\s.txt';
print @cmd;
EXEC master..xp_cmdshell @cmd;

Be sure there is no space or other character after the '^'.

edited Dec 27 '14 at 16:26

answered Dec 27 '14 at 16:01

Gordon Linoff

1,242,037
58
646
786

No, it doesn't create any file then, normally result information is: output (newline) ----- (newline) NULL, but whene somethins is wrong like here there is ------ (newline) Hello (newline) NULL and no file is created – user3762819 Dec 27 '14 at 16:05
Still no difference - 'Hello^world' works, but making ENTER between '^' and 'world' doesn't work :/ – user3762819 Dec 27 '14 at 16:49
@user3762819 . . . I'm out of ideas. I'll leave this answer here in case it helps anyone else trying to solve this problem. – Gordon Linoff Dec 27 '14 at 17:00

score 0 · Answer 2 · answered Mar 10 '23 at 21:09

How To add blank line in output/log/txt file Sample Code

BEGIN

--Ebnable xp_cmdshell
EXEC master.dbo.sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC master.dbo.sp_configure 'xp_cmdshell', 1;
RECONFIGURE WITH OVERRIDE;

DECLARE @cmd VARCHAR(255); DECLARE @var NVARCHAR(MAX) = ''; DECLARE @v_MyOutFile_v VARCHAR(20)= 'C:\MyOutFile.txt'; --Provide output filename with path

--Add 1st line
SET @var = '1st Line';
SET @cmd = concat('echo ', @var, '>> "', @v_MyOutFile_v, '"');
exec xp_cmdshell @cmd, no_output

--Add Blank line
SET @var = '& echo.'; --Blank line
SET @cmd = concat('echo ', @var, '>> "', @v_MyOutFile_v, '"'); --Do not add any spaces before >> otherwise it will be printed in the output file
exec xp_cmdshell @cmd, no_output

--Add 2nd line after blank line
SET @var = '2nd Line';
SET @cmd = concat('echo ', @var, '>> "', @v_MyOutFile_v, '"');
exec xp_cmdshell @cmd, no_output

--Disable xp_cmdshell
EXEC master.dbo.sp_configure 'xp_cmdshell', 0;
RECONFIGURE WITH OVERRIDE;
EXEC master.dbo.sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
END;

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Mar 16 '23 at 20:16

New line char in file using SQL

2 Answers2