search

microsoft / azuredatastudio

Azure Data Studio is a data management and development tool with connectivity to popular cloud and on-premises databases. Azure Data Studio supports Windows, macOS, and Linux, with immediate capability to connect to Azure SQL and SQL Server. Browse the extension library for more database support options including MySQL, PostgreSQL, and MongoDB.
https://learn.microsoft.com/sql/azure-data-studio
MIT License
7.6k stars 909 forks source link

Azure Data Studio fails to save to Excel/csv for medium to large size data sets #20463

Open johncquayle opened 2 years ago

johncquayle commented 2 years ago

Issue Type: Bug

  1. Execute query that returns >~100000 rows of results.
  2. Right click and save 'Save as Excel' or 'Save as CSV'.
  3. The 'Saving File' dialog will appear.
  4. The operation will never complete - the 'Saving File' dialog will remain until you close and reopen Azure Data Studio. Expected result is 'Successfully Saved Results to...' Dialog
  5. The output file gets created but remains empty, expected functionality is that the file would be populated with data.
  6. N.B. The same data set can be extracted by using SSMS 'Results to File' option but it's a nuisance to have to use SSMS for this.

Azure Data Studio version: azuredatastudio 1.39.0 (9fdb2161d49b4d4ab6798809faa042a898ca2eae, 2022-08-20T01:46:47.606Z) OS version: Windows_NT x64 10.0.17763 Restricted Mode: No

System Info |Item|Value| |---|---| |CPUs|Intel(R) Core(TM) i5-8365U CPU @ 1.60GHz (8 x 1896)| |GPU Status|2d_canvas: enabled
gpu_compositing: enabled
multiple_raster_threads: enabled_on
oop_rasterization: enabled
opengl: enabled_on
rasterization: enabled
skia_renderer: enabled_on
video_decode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled| |Load (avg)|undefined| |Memory (System)|7.81GB (1.36GB free)| |Process Argv|| |Screen Reader|no| |VM|0%|
Extensions (15) Extension|Author (truncated)|Version ---|---|--- demo-mode|dre|1.1.0 query-editor-boost|dre|0.4.1 sql-data-inspector|ern|0.4.0 azuredatastudio-select-top-n|jim|0.0.5 azuredatastudio-postgresql|Mic|0.2.7 import|Mic|1.5.0 query-history|Mic|0.2.1 server-report|Mic|0.2.3 extra-sql-script-as|pac|0.4.1 sqlops-spexecutesql-to-sql|pej|1.0.1 schema-visualization|R0t|0.7.1 sql-prompt|Red|0.2.11 sql-search|Red|0.4.0 simple-data-scripter|sea|0.1.6 vscodeintellicode|Vis|1.2.1

Does this issue occur when all extensions are disabled?: Yes/No

alanrenmsft commented 2 years ago

@johncquayle how long did you wait before you restart Azure Data Studio?

github-actions[bot] commented 2 years ago

We need more info to debug your particular issue. If you could attach your logs to the issue (ensure no private data is in them), it would help us fix the issue much faster. First open the Settings page, find the Mssql: Tracing Level setting and change that to All then restart ADS and repro your issue. Next there are two types of logs to collect:

Console Logs

Application Logs

johncquayle commented 2 years ago

I have left it overnight and it is still spinning in the morning.

SSMS would return the same data set into a CSV within an hour.

Thanks, John

On Tue, 30 Aug 2022 at 03:46, Alan Ren @.***> wrote:

@johncquayle https://github.com/johncquayle how long did you wait before you restart Azure Data Studio?

— Reply to this email directly, view it on GitHub https://github.com/microsoft/azuredatastudio/issues/20463#issuecomment-1230496719, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIAXQXLPRVD77ID55LN7NGTV3TLNPANCNFSM57QW4NJQ . You are receiving this because you were mentioned.Message ID: @.***>

johncquayle commented 2 years ago

console.log 20220830T095535.zip

marko-p commented 1 year ago

This might be related to the PostgreSQL extension. I'm experiencing this issue very very often (virtually always) with one particular script I run monthly on a PostgreSQL server, even though saving results as xlsx usually works just fine for other queries I run on that same server.

For a test, I transferred my data from PG to Sql Server, disabled the postgresql extension and adjusted the problematic script to run on Sql Server (and return the exact same results as from PG). I've ran the test script several times with different parameters and successfully saved the results to an excel file every time. Re-enabling the PG extension does not seem to have any influence on saving results from the test script.

When the problem occurs, the created XLSX file is empty, as in 0 bytes empty, it's not a valid Excel file.

Running on: Version: 1.40.2 (user setup) Commit: 661384637db384fe5d4e6224069adbe708580b16 Date: 2022-12-22T22:17:02.658Z VS Code: 1.67.0 Electron: 17.4.11 Chromium: 98.0.4758.141 Node.js: 16.13.0 V8: 9.8.177.14-electron.0 OS: Windows_NT x64 10.0.19045

System info: Processor Intel(R) Core(TM) i7-8665U CPU @ 1.90GHz 2.11 GHz Installed RAM 24,0 GB (23,8 GB usable) System type 64-bit operating system, x64-based processor

I've manually installed only the azuredatastudio-postgresql extension.

marko-p commented 1 year ago

I forgot to mention - I'm having problems even when trying to save small data sets (for example 133 records with 29 columns).

johncquayle commented 1 year ago

Has this been confirmed as being a problem with the Postgresql extension?

I often have problems saving output from MSSQL queries, not Postgresql, but it's not practical for me to restart Azure Data Studio every time I want to turn on the Postgresql extension.

alanrenmsft commented 1 year ago

ok, I'll take a look.

johncquayle commented 1 year ago

Confirmed that this is still a problem, even with the Postgresql extension disabled

johncquayle commented 1 year ago

The only workaround I've found, which is a real pain, is to run the query in SSMS to save the data.

moemura commented 5 months ago

This issue still valid for MySQL extension and ADS 1.48 Edit 69 columns x 1000 rows, for smaller column count it succed.