Maximize the power of SQL Prompt with SQL Toolbelt Essentials. Watch now.
Open in Excel Numbers to String

in SQL Prompt
When I right-click in the grid and choose Open in Excel, numbers are no longer converted to numerics in Excel. Now they are left-justified strings with the little green triangle warning that numbers are strings. This just started happening. I'm not sure exactly when, but I updated SQL Prompt last Friday. I'm running 10.12.3.28623 in SSMS 18.12.1
Tagged:
Answers
Hi @dkusleika
Thank you for reaching out on the Redgate forums regarding this export to Excel problem in SQL Prompt.
I can certainly see there were some changes made in v10.12.3 and sorry if this has caused an issue with your usage of the functionality.
Fixes
Fixed an issue where BIGINT data types were being exported to Excel in scientific notation.
Fixed a performance issue where typing in SSMS was laggy when large snippets are present.
Fixed an issue with an incorrect execution warning when using the ALTER TABLE syntax.
Added support for some SQL Server 2022 syntax, including LEDGER.
Fixed a crash that could occur when running the command line formatter.
Are you able to please provide a screenshot of the output and confirm the data type you are using for the number stored in SQL Server.
This will assist in replicating your issue closely and then provided to our development team to look into a solution.
In the interim I would suggest downgrading to v10.12.2 to see if the output is coming out as expected
https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.12.2.28458.exe
SELECT 1
Shows as numeric in Excel. A larger integer
SELECT 1234
Same. A decimal
SELECT 1.2
Comes in as text (left justified, green triangle in the top left). Maybe the decimal point is triggering something.
Without the decimal, it appears that 10-digit integers come over as numeric and 11 and up come over as text.
I am unable to confirm any resolution timeframe at this point but I would suggest monitoring our release note page for changes in v10.12.4 onwards
https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-10-12-release-notes
Unfortunately I have the same issue and now downgraded SQL Prompt. Sounds like someone's forgotten a semicolon
Version 10.12.3.28623 noted that a fix with exporting BIGINT data types was changed with that version. I suspect something done with that had broken the export of decimal and int to be treated as a string in excel.
For those not wanting to downgrade, exporting the results as csv still works, just adds a little more of a hassle.
Thank you to everyone who has advised they are having issues with the output to Excel functionality.
Our development team have confirmed they are working on a few reported problems in this functionality in v10.12.3.
I shall endeavor to update this post when we have a confirmed release that resolves these issues.
Otherwise, please keep an eye out for changes in v10.12.4 and onwards:
https://documentation.red-gate.com/sp/release-notes-and-other-versions/sql-prompt-10-12-release-notes