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

Question

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 data).

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

score 120 · Answer 1 · edited Feb 26 '18 at 13:37

120

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).

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

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

edited Feb 26 '18 at 13:37

rianjs

7,576
5
23
40

answered May 17 '16 at 19:19

Charles Gagnon

3,229
2
16
7

34

One more sidenote: I had to restart SSMS after selecting this option before it started working. – Wouter Nov 04 '16 at 09:57
2

This would have saved me hours if I had seen it sooner. I assumed the SSIS package was stripping out my line breaks. I also had to restart SSMS before this change took effect. – Eric Harlan Jun 27 '17 at 16:39
5

For me the changed setting already affected a new query window. No need to restart SSMS. – JanW Jul 20 '18 at 10:39
11

why is this not on by default?? – jtate Sep 05 '18 at 18:22
11

Did not have to restart SSMS but did have to close Query Editor window and open a new Query Editor window. – youcantryreachingme Sep 19 '18 at 03:03

score 3 · Answer 2 · answered Feb 19 '22 at 07:27

3

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

answered Feb 19 '22 at 07:27

Joe Shakely

328
4
6

The accepted answer already gives step-by-step instructions on how to get exactly there, no screenshot required. I suggest bookmarking the question instead of creating a superfluous screenshot-only answer. – Aaron Bertrand Mar 18 '22 at 17:25
2

Why would I read words when I could look at a picture? – Joe Shakely Mar 22 '22 at 04:03
Feel free to store a picture on your hard drive. It’s not an appropriate answer here, especially when it doesn’t add anything above the long-standing accepted answer. This site isn’t your personal reminder board. :-) – Aaron Bertrand Mar 22 '22 at 11:28
1

Seems like the exact appropriate answer to me. Is there a "No screenshots!" rule in stackoverflow? If so then delete it... – Joe Shakely Apr 07 '22 at 21:34
Actually yes. [This post](https://meta.stackoverflow.com/q/285551/61305) and several others like it focus mainly on questions but the concepts apply equally to answers. The image you’ve posted doesn’t add a single thing over the text `Tools > Options > Query Results > SQL Server > Results to Grid > Retain CR/LF…`. Never mind that it is way more bytes, might not always be available since it’s hosted elsewhere, and has absolute zero searchability. This site is once again not a dumping ground for photographs of your personal post-it notes. – Aaron Bertrand Apr 07 '22 at 22:05

score -2 · Answer 3 · answered Apr 21 '10 at 11:33

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.

I appreciate the effort, but this isn't any less work than selecting a single column at a time in text output mode or doing the print with a cursor. — Cade Roux, Apr 21 '10 at 12:36

score -2 · Answer 4 · answered Apr 29 '10 at 07:27

-2

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.

answered Apr 29 '10 at 07:27

adolf garlic

2,950
6
35
54

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

4 Answers4

Linked

Related