SQL Server/SSMS: Confusions around clear understanding of CR, LF, Tab: Settings does not work

Question

SQL Server/SSMS: Confusions around clear understanding of CR, LF, Tab: Settings does not work

Ashwin Kanumoori 40

I am observing that when SSMS settings are set to:

Scenario 1: Results to Grid

Case 1: Retain CR/LF values on copy or save is checked

Then, result shown in result tab, has a single line of text.

When I copy the text, and paste it in text file, then all escape characters - \r,\n are shown applied,

So the pasted content occupies more than one line based on CHAR(10), and CHAR(13) I have applied/inserted in the string

Case 2: Retain CR/LF values on copy or save is Un-checked

Then the same steps performed does not paste the results in multiple lines but in single line. Also, any escape sequence - char(10) or char(13) are replaced with single space.

Scenario 2: Results to Text (Just verifying the output text in result tab this context)

Scenario 3: Results to File (Just opening the file and verifying the text)

But in above 2 scenarios- 2 and 3, for both the cases -

case 1 : Retain CR/LF values on copy or save is checked

case 2 : Retain CR/LF values on copy or save is Un-checked

it gives the same results as case 1

So Does this work only for Results to Grid and

NOT

Results to Text and Results to File?

Or any other settings exist ?

Note:

Just to mention I am changing the settings and opening a new query window and executing.

(Also observed restarting SSMS)

Accepted answer

0 additional answers

Your answer

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

This picture should be a hint:

User's image

The setting is on the page for Results to grid. So it is quite logical that it only applies to grid results and not to text results or results to file.

As for the setting not applying to Results to text, it has always been the way that line breaks has resulted in line breaks in the output. The reason that Results to grid has this setting is that originally, CR/LF were always lost, because they were removed to fit the grid. But if you had a cell with something like SQL code, it wasn't fun to copy/paste. So they changed this with SQL 2012 to always retain line breaks. But then people who like to copy to Excel got mad. So eventually, they introduced this option to please everyone.

Ashwin Kanumoori 40 Reputation points

2023-10-08T07:52:13.3366667+00:00
Hi @Erland Sommarskog

Thank You, I am clear with your answer.

I thought I could ask the below Questions in the same thread based on my Question Title:

When I set 'Results to Export' option, I tried to export files - with file extension as .xls and .xlsx

(a) So when I opened the file with .xlsx extension, I am blocked with the pop up with OK button: Refer Attachment for details of message: XLSX_Warningpopup.jpg

(b) So, Though old and we should use it ideally, I exported with .xls format, When I opened the file with .xls extension, I am warned with popup and Yes, No, Cancel buttons. Clicking on Yes would display the result Refer Attachment for details of message: XLS_Warningpopup.jpg

Assuming '(a)' should be the case requiring the solution as we ideally don't use '(b)' .

2.When I export a file as .txt, it has the content as:

Refer Attachment: sc2.txt

So the dashes" --- .." are seen , along with the below:

(1 row affected)

Completion time: 2023-10-08T12:43:24.3338927+05:30
Is this expected ?

Also could you just list out the file types/extensions we can use to export using, 'Results to 'Export' ?
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-10-08T09:55:45.5533333+00:00
It does not matter which extension you give the file. It will still be a text file with the same output as when you select Results to text. So, yes, you get dashes, 1 row affected and completion time. (Although you can prevent 1 row affected with SET NOCOUNT ON, and SSMS permits you to turn off completion time.)

If you want to export data to Excel, there are three options:

Copy and paste from the grid into Excel.

Use Results to file after having change the output format in SSMS to comma or tab delimited.

In Excel use GetData and run the query from Excel.

I much recommend the last option. With the other methods, you fall prey of Excel's guessing of data types which can to all sorts of funny things. With GetData, Excel gets the data types from SQL Server and will be more obedient. Yes, this method is a little more bulky, and I tend to use Copy-and-paste for quick things. But if it's data I am going to send to someone, I use GetData. (A second benefit is that the query will be saved in Excel file.)

Share via

SQL Server/SSMS: Confusions around clear understanding of CR, LF, Tab: Settings does not work

0 additional answers

Your answer