Post reply

2008r2 vs 2014 (and 2012) - Ignoring 'tab/illegal' characters

  • November 9, 2016 at 5:03 am

    #401012

    Hello everyone,

    I hope you can help, I have a simple query, where I am copy/pasting grid results to Excel via SSMS output.

    When done via 2008r2 this works fine.

    When done via 2014 this doesn't work fine!

    Basically one of the text fields has a tab character at the end. In 2008r2 it seems to deal with this just fine, in 2014 it splits the row and messes up the output.

    Obviously correcting the data is one approach, but with this kind of stuff everywhere this isn't a (short term) option. Does anyone know why 2008 can handle this but later versions seemingly cannot?

    Any help much appreciated.

  • November 9, 2016 at 5:11 am

    #1911372

    I don't have SQL2014 but perhaps take a look at the settings in SSMS:

    > Tools - options - Query results - SQL Server - Results to Grid - "Retain CR/LF on copy or save"

    There may be some other settings also...

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • November 9, 2016 at 5:25 am

    #1911378

    I've just seen that but it doesn't actually seem to be an option in 2014... not sure if it's only in 2012 or 2016 :/

    This suggests it's in 2016: http://www.sqldiablo.com/2016/06/10/sql-2016-retain-crlf-when-copying-from-results-grid/

  • November 9, 2016 at 5:31 am

    #1911381

    You could replace the tab character with a space or an empty string in the SELECT statement. If you don't want to save the tab characters at all, the other approach is to clean the data as it's being saved.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 9, 2016 at 5:33 am

    #1911382

    Rob-350472 (11/9/2016)

    I've just seen that but it doesn't actually seem to be an option in 2014... not sure if it's only in 2012 or 2016 :/

    This suggests it's in 2016: http://www.sqldiablo.com/2016/06/10/sql-2016-retain-crlf-when-copying-from-results-grid/%5B/quote%5D

    Yeah, you're right. I have checked and it is also not available in SSMS 2012. As you don't have it in SSMS 2014 it's obviously a new SSMS 2016 option :w00t:.

    Do you still have SSMS 2012 available? If so, you can compare all settings in the options menu between SSMS 2012 (which works like you want) and SSMS 2014... Like I said before: there could be other settings also.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • November 9, 2016 at 5:52 am

    #1911389

    The fix would seem to be 2016.... it does the same in 2014 and 2012, I''ve moved to Win10, so 2008r2 isn't officially supported. Grr!

  • November 9, 2016 at 6:42 am

    #1911400

    And after an upgrade, 2016 is installed and seems to work as intended 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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