With every new release some of the default behavior can surprise because you are so much used to them. But when they don’t behave the same, it baffles to when some of these changes were introduced. In pursuit of finding such behavior, while I was working with SQL Server 2016 Management Studio, I was surprised to see a behavior which didn’t seem to be right. You can also try it too.
Here is the query which would demonstrate the behavior.
SELECT 'Pinal Dave is an owner of SQLAuthority.com'
This command is in three line and there is a carriage return at the end of each line. Once we run above query in grid mode, we would see below in SQL Server 2014 Management Studio and SQL Server 2016 Management Studio.
Yes, you are right! There is no difference in output. Now, here is the fun part – Copy the results of the grid and paste it in any text editor: SSMS or Notepad or Word or anywhere you want.
Did you see the difference in behavior? Here it is in Notepad.
When we copy paste from SSMS 2016, it removed carriage return and complete text came in single line. I think that’s something which few people may not like. I searched further on documentation and found setting to change this behavior in SQL Server 2016 Management Studio. It didn’t take much time to find below setting (Can be reached by Tools > Options in SSMS menu bar)
By default, its unchecked and that why it’s not retaining CR/LF (carriage return / Line Feed). Once I checked the box, I was able to get earlier behavior. Do have such requirements in your system? Do let us know.
Reference: Pinal Dave (https://blog.sqlauthority.com)
40 Comments. Leave new
The setting should be selected by default. Data integrity above all else. Why would a data-centric tool do anything that alters data? That doesn’t make sense. The systems I work on wouldn’t necessarily break due to this change, but I can think of many situations where eliminating CRLF would cause cosmetic problems if copy/paste were used while in edit mode using SSMS.
It’s almost like they’ve turned every new tool on just to showcase it, even if it’s not necessarily what the majority of people would use within their workflow. More specifically I’m thinking back to Pinal’s recent blog about the new line highlighting in SSMS 2016, I remember thinking that it felt like an ease of use/accessibility feature that should also have been off as default.
I also agree with Tom, should be a default option, but is good to know this in advance. Thanks
My guess is so that you can copy/paste the results into Excel easily.
That can cause a lot of headaches, it should be the default. Thanks for the tip
yes it is better to use the Excel
It’s worth noting that changes to this setting appear to only take effect with new tabs. Changing the setting didn’t alter the behavior of tabs / result sets I already had open.
you can alter the query options of any current tab by clicking the query options button on the toolbar
Thanks @Vivan
same here. thanks for pointing this out
Very useful. Thanks a lot!
Thank you for the fix, I’m glad google led me to you :-)
Thanks to Google.
My cheese!
Thank you Dave for the blog. This was a pain as all my old “script generating” scripts suddenly lost all the formatting!
My pleasure myladoor.
You absolute hero!
Thanks James.
After upgrading from Office 2010 to Office 2016, it appears that pasting code from SQL Text Editor 2014 to PowerBuilder suddenly loses line breaks Any thoughts on why this might happen?
I don’t know. Never heard.
Slightly off topic, but in SSMS 2012 – I am copying some data from a table in a database in a test server to a table in a database in a live server. One of the varchar fields contains lots of data with carriage returns in. When I paste this into the table in the live database – anything after the first carriage return is not pasted and/or throws an error and the row is not copied. I am having to manually copy and paste the contents of the columns that are causing the problem. It is taking hours – instead of minutes. Is there a solution
What is the method you are using to move the data from one server to another?
I am having this same issue with SQL Server 2014. There is no option listed as you provide in your example. There is however, ‘Quote Strings containing list separators when saving .csv files’. I tried both options, on/off, without success. Any ideas?
Awesome!!! The only response I could find with the right answer!!! Thank you!
I am Looking a case in Vice versa, In 2014 when I copy the text from Query Results the lines shouldnt be lf/cr. I need it completely in one line(excel cell)
Hi Guys
What if this does not work? I was running v17.7, no issue’s last week and suddenly this week this issue appears, I have upgraded to 17.8 in a bid to fix the issue, no luck, I have ensured the above option is ticked and I still get line breaks when copying to excel.
My colleague does not have this issue with the same version of mssms, I even have a remote machine with SQL 2008 on and it does not have this issue!
My current work around is to save as csv which I can live with I guess but not being able to copy/paste straight from the table is an inconvenience indeed.
Kind Regards
Andy
Thanks. Very helpful
I was trying to retain the CR/LF for use in Excel. I found that I had to check the “Retain CR/LF on Copy or Save” and then when I had the results of my query displayed in SSMS, then I right mouse clicked on them and chose “Save Results as” and saved as a CSV file. Once I opened this CSV in Excel, then the newlines were retained in the column I desired. Thanks for the tip.