How can we help you today? How can we help you today?

An undocumented change in 'Open in Excel'

Just noticed something that was different...

I love the 'Open in Excel' right click option in SQL Prompt.  I use it regularly to extract some quick results that I need to send to people or include in a document or email. (at lot of times it's lookup table values)
I just noticed something different after upgrading to SQL Prompt 10.3.1 from 10.2.1. 
A SQL query that returns results with bit field in SSMS shows the bit field in the grid as a '0' (for false) or '1' (for true). 




I used SQL Prompt 10.2.1 on Jul 2 and after doing an 'Open in Excel' it showed the bit field in Excel as a 0 or 1.  Pretty standard. 



After upgrading to 10.3.1, now when I use the 'Open in Excel' function the '0' or '1' are converted to the words 'True' or 'False' in the Excel file.


It's a minor issue but it did cause some grief.  I sometimes use some boiler plate Excel templates where I paste the SQL Prompt Excel file results into another work sheet.  These sheets have formulas that look for 0 or 1.  Those formulas stopped working until I fixed them.  I know it's not pretty doing this, but this change just caught me off guard.  I looked in the SQL Prompt options to see if there were any configuration settings for the 'Open in Excel' feature, but I didn't find any.  Maybe something for the future.

I want to reiterate that I love this feature.  It's the best solution I've found for quickly copying some results out to Excel.
The best part is that it just works.  Simple and quick.

(Also as reviewed by Louis Davidson)

CurtisGregson
0

Comments

7 comments

Sort by
  • Tianjiao_Li
    Hi @CurtisGregson

    I can indeed see this behaviour in 10.3.1. However, it seems to behave the same way in 10.2.1 for me.

    Looking through some historical data, this looks like a feature request. Could you let me know the version number of your SSMS?
    Tianjiao_Li
    0
  • CurtisGregson
    Hi Tianjiao.

    I am using SSMS 18.5.1.  Interesting that you see the same issue with 10.2.1.  I've been using this feature for quite a while and it always worked as I had described.  It was only after the 10.3.1 upgrade that it changed for me. 
    CurtisGregson
    0
  • Tianjiao_Li
    Would you be able to share your SQL Prompt options? SQL Prompt -> Options ->Export

    Thanks!
    Tianjiao_Li
    0
  • CurtisGregson
    Sure thing.. here is my options export...


    CurtisGregson
    0
  • Tianjiao_Li
    @CurtisGregson

    Thanks for sharing your options. It didn't change anything for me, unfortunately. However, you could use 'Save result As...' as a workaround:



    Tianjiao_Li
    0
  • CurtisGregson
    Well okay...   but just to be clear... 'Save Results As...' is not really an efficient or effective work around.
    I spent the time testing previous versions and I can reproduce the issue...  but it appears this change has sneaked into the app so I will have to adjust to it.
    CurtisGregson
    0
  • Tianjiao_Li
    @CurtisGregson

    So sorry for the inconvenience this is causing you. The behavior we currently see is the expected behavior, sorry about the inconsistency you've experienced. We appreciate if you could kindly leave your suggestion on our user voice forum https://redgate.uservoice.com/forums/94413-sql-prompt
    Tianjiao_Li
    0

Add comment

Please sign in to leave a comment.