none
SQL pass-through query RRS feed

  • Question

  • Hi,

    We have a DWH build on DB2 with large amounts of data in our company. I as a super user of the data (not IT guy) have direct read access to the various tables. The connection is made via ODBC. We are using an IDAA accelerator and to tell DB2 I want to make use of it, every SQL select statement must start with “set current query acceleration = all;”…

    In the previous version of Power Query select queries starting with the mentioned pass-through query worked just fine. After installing the latest update I get the following error message “Expression.Error: This native database query isn't currently supported.” when using “set current query acceleration = all;” before the actual select statement.

    Is it still possible to use pass-through queries with Power Query?

    Second big annoyance with PQ is that you can’t set it to be prompted for credentials every time you run a query. We change our DWH credentials on a regular schedule but PQ still tries to access the data with the old credentials and gets me locked out after so many false attempts… Annoying! An option to be prompted for credentials every time would be so much more secure and convenient in my case.

    Thanks for any answers and KR,

    Janez

    PS, I am using the 32-bit version of PQ as an add-in with 32-bit Excel 2013 on a Windows 7 machine.

    Wednesday, May 18, 2016 9:54 AM

Answers

  • This is a regression in the ODBC connector. We used to call SQLMoreResult until we encountered a result set with data. It seems like now we simply expect the data to be in the first result set.

    We've filed a bug to get this fixed. In the meantime, there is no work around besides changing the query such that the first statement is the one returning the data. I understand that in your case, this may not be possible. We'll try and get this fixed for the next release, probably sometime in June.

    Monday, May 23, 2016 5:28 PM
    Moderator

All replies

  • What version did you use previously use in which you say the SQL query worked?
    Wednesday, May 18, 2016 6:57 PM
    Moderator
  • in version PowerQuery_2.27.4163.242 (32-bit) [en-us].msi it was working fine.

    In PowerQuery_2.33.4337.481 (32-bit) [en-us].msi is not working.


    • Edited by JanezV Thursday, May 19, 2016 6:23 AM
    Thursday, May 19, 2016 6:22 AM
  • This is a regression in the ODBC connector. We used to call SQLMoreResult until we encountered a result set with data. It seems like now we simply expect the data to be in the first result set.

    We've filed a bug to get this fixed. In the meantime, there is no work around besides changing the query such that the first statement is the one returning the data. I understand that in your case, this may not be possible. We'll try and get this fixed for the next release, probably sometime in June.

    Monday, May 23, 2016 5:28 PM
    Moderator