SSMS Results to Grid - CRLF not preserved in copy/paste - any better techniques?

When I have a result set in the grid like:

SELECT 'line 1
line 2
line 3'

or

SELECT 'line 1' + CHAR(13) + CHAR(10) + 'line 2' + CHAR(13) + CHAR(10) + 'line 3'

With embedded CRLF, the display in the grid appears to replace them with spaces (I guess so that they will display all the d开发者_如何学JAVAata).

The problem is that if I am code-generating a script, I cannot simply cut and paste this. I have to convert the code to open a cursor and print the relevant columns so that I can copy and paste them from the text results.

Is there any simpler workaround to preserve the CRLF in a copy/paste operation from the results grid?

The reason that the grid is helpful is that I am currently generating a number of scripts for the same object in different columns - a bcp out in one column, an xml format file in another, a table create script in another, etc...

This issue has been fixed in SSMS 16.5 build 13.0.16000.28 with the addition of an option to preserve CR/LF on copy/save (more details) (Connect bug).

1. Tools > Options
2. Expand Query Results > SQL Server > Results to Grid
3. Tick Retain CR/LF on copy or save
4. Restart SSMS

This will cause CR, LF, and CRLF to be treated as newlines when you copy a cell.

Answering this for myself because I can never remember where this is:

Warning: There's definitely some kind of bug still with this feature.

First of all, I haven't touched the option in months and have recently rebooted.

I had a query with several columns, one of which contained customer feedback (with linefeeds). When I pasted the results into Google Docs / Excel the feedback went into one line (as I wanted).

I then copied the query to another file and ran it again. This time the results contained line breaks!

So either there is a very odd bug, or some secret shortcut that changes the setting for the current window. Interested if anyone else sees this behavior.

it is a hack, but try this:

wrap your result set in a REPLACE (.....,CHAR(13)+CHAR(10),CHAR(182)) to preserve the line breaks, you can then replace them back

SELECT 
    REPLACE ('line 1' + CHAR(13) + CHAR(10)+ 'line 2' + CHAR(13) + CHAR(10) + 'line 3'
            ,CHAR(13)+CHAR(10),CHAR(182)
            )

OUTPUT:

----------------------
line 1¶line 2¶line 3

(1 row(s) affected)

replace them back in SQL:

select replace('line 1¶line 2¶line 3',CHAR(182),CHAR(13)+CHAR(10))

output:

-------------------
line 1
line 2
line 3

(1 row(s) affected)

or in a good text editor.

One thing you can do is send results to a file, then use an editor capable of watching a file for changes which has superior capabilities for understanding the output.