Forum Discussion
Excel Version 1907 [Expression.Error] This native database query isn't currently supported
For the time being, it's suitable enough for our purposes to have the dynamic query/pivot run refresh a cache table hourly with a Drop if exists drop table and a SELECT ... INTO ... in a separate stored proc, and update the Excel report to pull from the cache table (it's not ideal, but it has fixed the issue for now) until hopefully a future update resolves it properly and we can go back to the report being live
If the number of columns and types are consistent in your dynamic query, I have a theory that putting a SELECT '','',GETDATE(),0,0 (for example, with the appropriate types and number of columns) at the end of the stored proc may get it to work - I think the error is because it can't predict the column types. Doing this with the wrong number/type of columns (my dynamic query is not consistent) gives a different error which is why I think it may work, (and my assumption is only the first result set from the dynamic query will actually display)
I also have a theory that enabling "Legacy" SQL data imports and putting in the query the old way (i.e. how excel 2007 used to do it, where the query itself is in the properties pane instead of only editable in power query editor) would resolve the issue, but I couldn't remember / easily work out how to find this so haven't tried it
Fleck2019, brucebdatacom, sthompsonSelectSires
I recently applied the 1908 monthly update and tested my dynamic query again and it appears to work. I would like to know if the update works in your case as well.
Only just looked at this again - reverted my cache table change, and it is indeed working on the version you mentioned, thank you for the reminder!
- Yes. Updating to 1608 has also resolved our issue.
