How to retain cr/lf characters in query output.

Question

How to retain cr/lf characters in query output.

JustASQLGuy

SSC-Addicted

Points: 447
More actions
October 6, 2009 at 10:33 am

#215112

For example, querying for longest running queries, etc, can use the sys.dm_exec_sql_text function, which returns the text/definition of a query or other code snippet. Those results should contain the appropriate cr/lf, but somehow those are suppressed in the result window of the query. Is there a way to retain those special chacters? I am using 2005.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

when in gridview in SSMS (Control D) the CrLf appear to be supressed. but if you switch to text vie (Control T) and rerun your statement, the CrLf's will be preserved....

the grid view ignores/does not present the CrLf, but they are still there in the data. it's purpose is to present the data in a more compact format, and cannot scroll like a text file in the cell used for presentation, so the app hides them in that mode.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JustASQLGuy SSC-Addicted Points: 447 More actions · Answer 2

Thanks Lowell. This solution works somewhat. I see that the results get truncated, whereas in the grid view, they are not. Is there a setting that controls how much of the text will display for "Results to Text"?

-- Found that setting in the options. Thanks again.

Lowell SSC Guru Points: 323518 More actions · Answer 3

it's getting truncated because there is ANOTHER setting to limit the results to 256 chars by fdefault:

go here and change the value to 8000:

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!