87

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

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
Cade Roux
  • 88,164
  • 40
  • 182
  • 265

5 Answers5

156

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.

rianjs
  • 7,767
  • 5
  • 24
  • 40
Charles Gagnon
  • 3,619
  • 2
  • 17
  • 7
  • 47
    One more sidenote: I had to restart SSMS after selecting this option before it started working. – Wouter Nov 04 '16 at 09:57
  • 3
    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
  • 8
    For me the changed setting already affected a new query window. No need to restart SSMS. – JanW Jul 20 '18 at 10:39
  • 16
    why is this not on by default?? – jtate Sep 05 '18 at 18:22
  • 14
    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
  • Did have to restart SSMS, as closing the Query Editor window and opening a new Query Editor window did not pick up the change. – Ian Boyd Dec 21 '22 at 19:27
9

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

enter image description here

Joe Shakely
  • 623
  • 7
  • 9
  • 1
    A mnemonic based on the acronym for Tools Options Queue (Results) S(ql server) R(esults to) Grid: The Optimal Quest Should Return Gold – George Menoutis Dec 30 '22 at 11:00
2

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.

Simon_Weaver
  • 140,023
  • 84
  • 646
  • 689
-3

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.

KM.
  • 101,727
  • 34
  • 178
  • 212
  • 3
    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
-3

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.

adolf garlic
  • 3,034
  • 7
  • 39
  • 54