line breaks lost in sql server

Question

I am entering error information into an ErrorLog table in my database. I have a utility class to do this:

ErrorHandler.Error("Something has broken!!\n\nDescription");

This works fine. However, when I try to access this table, the line breaks no longer seem to be present.

If I SELECT the table:

SELECT * from ErrorLog ORDER BY ErrorDate

there are no line breaks present in the log. This is kind of expected, as line breaks in one-line rows would break the formatting. However, If I copy the data out, the line break characters have been lost, and the data is all on one line.

How do I get line breaks in data at the end of my query when I put line breaks in? I don't know if the string has been stripped of line breaks when it enters the table, or if the viewer in SQL Server Management Studio has stripped out the line breaks.

The data type of the column into which error messages are put is nvarchar(Max), if that makes a difference.

EDIT: Unexpectedly, Pendri's solution didn't work.

Here is an excerpt of the string just before it passes into the SQL server:

POST /ipn/paymentResponse.ashx?installation=272&msgType=result HTTP/1.0\n\rContent-Length: 833\n\rContent-Type:

And here is the same string when I extract it from the grid viewer in SQL Server Management Studio:

POST /ipn/paymentResponse.ashx?installation=272&msgType=result HTTP/1.0  Content-Length: 833  Content-Type:

The place where the line break should be has been double spaced.

Any ideas?

When you say line breaks are lost, is that because they don't appear in the grid results view when you run the query from SSMS? I believe SSMS strips line breaks in results, even when you copy and paste. You could verify this by casting to varbinary and seeing if the relevant character codes are present in the binary representation, or by writing a small c# app. — Theo Spears, Dec 05 '11 at 16:51

score 81 · Answer 1 · edited May 23 '17 at 11:54

81

No need to replace string input\output, you need just pick up correct option:

Tools -> Options...

> Query Results 
  > SQL Server 
    > Results to Grid 

set "Retain CR\LF on copy or save" to true.

And don't forget to restart your management studio!

according Charles Gagnon answer

edited May 23 '17 at 11:54

Community

1
1

answered Sep 29 '16 at 07:09

n1k1t0ss

1,021
1
8
5

4

I had to restart SSMS after changing this setting and then it worked. I think this addresses the question more directly than the accepted answer. – asontu Oct 19 '17 at 12:32
6

This answer needs more votes. p.s. You only have to create new query window; you don't have to restart after changing the option. – mcNux Nov 21 '17 at 17:58
5

This just sets the default for new query windows. If you want to change it for an existing query window, right-click in the query pane, select Query Options, then go to Results > Grid and check 'Retain CR/LF on copy or save'. (You can also get to Query Options from the Query menu.) – Mike Dimmick Sep 03 '19 at 13:50
1

This wasn't working at first for me (SSMS 13.0.15500.91). I saw the comment to restart SSMS to get the changes to stick, but I didn't want to because I had a lot of unsaved work. I opened a new instance of SSMS and the changes worked without closing any already open windows. – Patrick Tucci Apr 02 '20 at 17:09
You have saved me, appreciate this! – kdawg Jun 03 '21 at 17:12

score 29 · Accepted Answer · answered Dec 05 '11 at 16:49

29

SSMS replaces linebreaks with spaces in the grid output. If you use Print to print the values (will go to your messages tab) then the carriage returns will be displayed there if they were stored with the data.

Example:

SELECT 'ABC' + CHAR(13) + CHAR(10) + 'DEF'
PRINT 'ABC' + CHAR(13) + CHAR(10) + 'DEF'

The first will display in a single cell in the grid without breaks, the second will print with a break to the messages pane.

A quick and easy way to print the values would be to select into a variable:

DECLARE @x varchar(100);
SELECT @x = 'ABC' + CHAR(13) + CHAR(10) + 'DEF';
PRINT @x;

answered Dec 05 '11 at 16:49

Tarwn

1,030
8
7

1

Great! This works. It's a bit inconvinient having to do this just to get data out though. – Oliver Dec 05 '11 at 17:00
1

Easier: right click in query window, "Results To | Results to Text" – dudeNumber4 Nov 27 '19 at 15:30

score 7 · Answer 3 · answered Dec 24 '13 at 18:05

7

Update a couple years later.

As described here, one solution to preserve viewing linebreaks in SSMS is to convert the output to XML:

SELECT * FROM (
  SELECT * from ErrorLog ORDER BY ErrorDate
) AS [T(x)] FOR XML PATH

Fortunately, if you have SSMS 2012, this is no longer an issue, as line breaks are retained.

answered Dec 24 '13 at 18:05

David C

7,204
5
46
65

2

You should use the "TYPE" keyword. See my answer: http://stackoverflow.com/a/30927549/2266979 – Riley Major Jun 18 '15 at 23:31

score 3 · Answer 4 · edited May 23 '17 at 12:10

I echo David C's answer, except you should use the "TYPE" keyword so that you can click to open the data in a new window.

Note that any unsafe XML characters will not work well with either of our solutions.

Here is a proof of concept:

DECLARE @ErrorLog TABLE (ErrorText varchar(500), ErrorDate datetime);
INSERT INTO @ErrorLog (ErrorText, ErrorDate) VALUES
    ('This is a long string with a' + CHAR(13) + CHAR(10) + 'line break.', getdate()-1),
    ('Another long string with' + CHAR(13) + CHAR(10) + '<another!> line break.', getdate()-2);
SELECT
    (
        SELECT  ErrorText AS '*'
        FOR XML PATH(''), TYPE
    ) AS 'ErrorText',
    ErrorDate
FROM        @ErrorLog
ORDER BY    ErrorDate;

I can confirm that the line breaks are preserved when copying out of a grid in SSMS 2012.

score 1 · Answer 5 · answered Nov 20 '19 at 21:08

1

Another simple solution is to click the "results to text" button in SSMS. Its not super clean, but gives you visibility to line breaks with about half a second of work.

answered Nov 20 '19 at 21:08

TizzyFoe

1,489
1
15
28

score 1 · Answer 6 · answered Dec 05 '11 at 15:57

1

try using char(13) + char(10) instead of '\n' in your string (define a constant and concatenate to your sql)

answered Dec 05 '11 at 15:57

penderi

8,673
5
45
62

Would it work in all cases if I did something like `message.replace('\n',char(13) + char(10))` in my `ErrorHandler.Error` method? – Oliver Dec 05 '11 at 16:04
2

The data is getting into the table. The problem is the way SSMS is displaying and handling the data. – Riley Major Jun 18 '15 at 20:51

score 0 · Answer 7 · answered Apr 18 '23 at 15:43

0

Please, could you try to change/dicover the SQL - Tools - Option settings

https://www.linkedin.com/posts/daniele-scordamaglia-460a78a5_sqlserver-sql-cr-activity-7053663883944701952-G8It?utm_source=share&utm_medium=member_desktop

answered Apr 18 '23 at 15:43

ulisses

1,549
3
37
85

score -1 · Answer 8 · edited Jul 19 '19 at 13:46

For SQL Server 2008, there is no provision to set "Retain CR\LF on copy or save" to true.

For this issue, what I did is that, replace char(13) with "\r" and replace char(10) with "\n" like below.

REPLACE(REPlACE([COLUMN_NAME],char(13), '\r'),CHAR(10),'\n')

And in the code-behind again I've replaced "\r\n" with a break tag.

I worked out in this way as there was no option provided in SQL 2008 as mentioned above. This answer might be an alternative though.

Thanks

line breaks lost in sql server

8 Answers8

Linked