08-08-2019 05:43 PM
08-08-2019 05:43 PM
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?
08-12-2019 01:54 PM
We are having the same issue Works on 1902 fails on 1907 @TheCriticalPath
08-13-2019 06:30 AM
@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
08-13-2019 06:33 AM
@Fleck2019. The dynamic query is an excellent point and is relevant in our case.
08-13-2019 11:22 PM
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
08-14-2019 01:18 AM
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
08-27-2019 09:42 AM
08-27-2019 07:29 PM
09-18-2019 08:28 AM
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!