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

Description");

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

Content-Length: 833

Content-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?

Answer 1

No need to replace string inputoutput, you need just pick up correct option:

Tools -> Options...

> Query Results 
  > SQL Server 
    > Results to Grid 

set "Retain CRLF on copy or save" to true.

And don't forget to restart your management studio!

according Charles Gagnon answer