4

I have a problem with consistency on different SQL Server versions. With the following code:

DECLARE @text NVARCHAR(50)
SET @text = 'This is line 1.' + CHAR(13)+CHAR(10) + 'This is line 2.'
SELECT @text

I copy and paste results into notepad

From SQL Server 2008R2 shows

This is line 1. This is line 2.

From SQL Server 2014 shows

This is line 1.
This is line 2.

Any idea how I can fix this? Or why it is like this? I want to achieve the latter.

Thanks in advance

Lemniscate
  • 41
  • 1
  • 1
    Try `PRINT` instead of `SELECT`. – Felix Pamittan Oct 12 '16 at 03:46
  • 6
    Unfortunately, there is no solution to this for select statements in SQL Server 2008. It was fixed in later versions though (from 2012, the "bug" doesn't exist. In 2016 I believe it's off by default but can be adjusted by going to Options -> Query Results -> SQL Server -> Results to Grid -> Retain CR/LF... This isn't necessary in 2012/2014 and there's no backwards compatibility for 2008). I tried this in SQL 2008 and a quick look at the hex of the output when selecting @text shows the carriage return/line feed are replaced by spaces. – ZLK Oct 12 '16 at 04:20
  • 1
    Here's a different stack overflow thread on the issue (from 2010 so presumably using SQL 2008): http://stackoverflow.com/questions/2679481/ssms-results-to-grid-crlf-not-preserved-in-copy-paste-any-better-techniques - I haven't tried anything in the marked answer but you might find an idea there that works. – ZLK Oct 12 '16 at 04:29
  • @ZLK thank you, I suppose I could just use the newer management studio :) – Lemniscate Oct 12 '16 at 09:34

1 Answers1

0

I tried in sql server 2008 R2(SP2). It does not have the issue you specified.

Also a tip is that you can change the results to show in Text, as opposed to Grid, so you can see the output easily without having to copy and paste.

Naz

Nazer Mohamed
  • 38
  • 5