locked
Native Database Query dialog box RRS feed

  • Question

  • Are there specific circumstances that force the display of the Native Database Query dialog when a query is being refreshed? I can't find any pattern, but the dialog box popping up is very annoying.
    Friday, June 20, 2014 4:45 PM

Answers

  • Each distinct query has to be approved by the user exactly once for a given data source. If you're generating queries dynamically, then the user may end up being prompted frequently.
    Friday, June 20, 2014 6:56 PM
  • If you're using SQL Server or MySQL, we do support some kind of server-side code -- table-valued functions in the case of SQL Server and I'm afraid I don't know what in the case of MySQL. These can be used with parameters and do not require a prompt.

    Friday, June 20, 2014 10:53 PM

All replies

  • Each distinct query has to be approved by the user exactly once for a given data source. If you're generating queries dynamically, then the user may end up being prompted frequently.
    Friday, June 20, 2014 6:56 PM
  • Which implies that passing parameters to an SQL query in many scenarios is useless, since exposing that dialog box would be confusing to an end user. The only reason I've resorted to using a native SQL query is that it can be more than an order of magnitude faster than the equivalent query using M. I think that I've finally come to the conclusion that while some problems are solvable in Power Query, performance limitations may make these solution impractical.

    Thanks for the quick response.

    Friday, June 20, 2014 7:26 PM
  • If you're using SQL Server or MySQL, we do support some kind of server-side code -- table-valued functions in the case of SQL Server and I'm afraid I don't know what in the case of MySQL. These can be used with parameters and do not require a prompt.

    Friday, June 20, 2014 10:53 PM