Excel Version 1907 [Expression.Error] This native database query isn't currently supported

Copper Contributor

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

We are having the same issue Works on 1902 fails on  1907 @TheCriticalPath 

@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

@Fleck2019.  The dynamic query is an excellent point and is relevant in our case.

 

 

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 

 

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.

Yes. Updating to 1608 has also resolved our issue.

@TheCriticalPath 

 

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!