1

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

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
user3762819
  • 105
  • 10

1 Answers1

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 '^'.

Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
  • 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