Forum Discussion
Excel Version 1907 [Expression.Error] This native database query isn't currently supported
I have a file that contains a data connection that has been working for months. It recently stopped working after the 1907 update applied.
Is anyone else experiencing an issue? How might I go about determining what part of the native query is causing the issue? I have also converted the query to a stored procedure and it doesn't work either. Is there a way to get more detailed error information?
8 Replies
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!
Hi TheCriticalPath. We have also come across this. One query, dynamic sql with a pivot table started failing, others in the same workbook continued to run fine.
I rolled Office 365 back from 1907 to 1906 and the issue was resolved. Waiting for 1908 now to see if is fixed 🙂
TheCriticalPath We are having the same problem, though it is only with one query
The stored procedure we run uses dynamic SQL as well as a linked server - this may be relevant so mentioning it in-case it helps, but I haven't resolved the issue yet
We are having the same issue Works on 1902 fails on 1907 TheCriticalPath
