New Line Issue when copying data from SQL Server 2012 to Excel

Question

I recently upgraded to SQL2012 and am using Management Studio. One of my columns in the database has a CHAR(13) + CHAR(10) stored in it.

When I was using SQL Server 2008, this would copy and paste completely fine into Excel. Now, however, copying and pasting the same data creates a new line/ carriage return in the data I have in Excel.

Is there a setting I missed in SQL2012 that will resolve this issue? I don't want to simply REPLACE(CHAR(13) + CHAR(10)) on every single database selection, as I would have to go from using SELECT * to defining each individual column.

Answer 1

My best guess is that this is not a bug, but a feature of Sql 2012. ;-) In other contexts, you'd be happy to retain your cr-lf's, like when copying a big chunk of text. It's just that it doesn't work well in your situation.

You could always strip them out in your select. This would make your query for as you intend in both versions:

select REPLACE(col, CHAR(13) + CHAR(10), ', ') from table 

Answer 2

This is fixed by adding a new option Retain CR\LF on copy or save under the Tools -> Options... menu, Query Results -> SQL Server -> Results to Grid.

You need to open new session (window) to make the change take a place.

The default is unselected (false) which means that copying/saving from the grid will copy the text as it is displayed (with CR\LF replaced with spaces). If set to true the text will be copied/saved from the grid as it actually is stored - without the character replacement.

In case people missed following the chain of connect items (leading to https://connect.microsoft.com/SQLServer/feedback/details/735714), this issue has been fixed in the preview version of SSMS.

You can download this for free from https://msdn.microsoft.com/library/mt238290.aspx, it is a standalone download so does not need the full SQL media anymore.

(Note - the page at https://msdn.microsoft.com/library/ms190078.aspx currently isn't updated with this information. I'm following up on this so it should reflect the new option soon)