cancel
Showing results for 
Search instead for 
Did you mean: 

Support 'Execute a SQL query' action through an on-premises data gateway

The “Execute a SQL query” action is not currently supported in Microsoft Flow using an on-premises gateway connection. 

 

The "Get rows" action allows users to pull records from a single table, but there is currently no way to pull records from multiple on-prem SQL tables. To avoid executing a complex query from Flow, some users have combined "Execute a stored procedure" to build a single table with "Get rows" to query it, but this workflow is also not supported with an on-premises data gateway.

 


Adding this functionality would create a great deal of opportunities for automation of testing and reporting on complex data structures.

Executing a Flow with this connector yields the following error:
{
  "status"400,
  "message""Gateway ExecutePassThroughNativeQueryAsync\r\nclientRequestId: 0a3e8a93-45a6-4f29-ad28-d4375b6963a7",
  "source""sqlconnectionprovider-westus.tip0-westus.p.azurewebsites.net"
}

 

 Flow Error.PNG

 

Status: Completed
Comments
ucfhall
Regular Visitor

Only problem with this approach is that the query is fixed and Power Automate is not set up to pass parameter values. This is a showstopper for us unfortunately. Good find...but Microsoft needs to stop bullying businesses to use Azure SQL by refusing to properly support on-premises SQL servers.

trjackson77
Resolver I

Why does this idea have the 'New' label?  It is years old...

Boyan
Frequent Visitor

@ucfhall That's a good point. It is static. Duly noted. Of course the optimal outcome here is for Microsoft to listen and "fix" this terrible architecture decision but in 30 years in this biz I've always failed to compel Microsoft in that simple skill - listen to the customers 🙂 

jpshank2
Regular Visitor

Multiple docs online say this is possible. Could we match the functionality to the docs? That would save a huge amount of frustration

CromeSchool
Regular Visitor

This isn't just a problem for querying on-premise databases.  For security reasons, I'm needing to query my azure sql databases via an on-prem gateway (which has express route access to my databases).

 

My understanding is that in order to query azure sql from the power platform you need to whitelist the power platform ip ranges OR simply allow access to all azure services, neither of which is ideal.  (Very happy to hear if my understanding is wrong).  So this is the reason why I'm wanting to use an on-prem gateway.

 

Very keen to see this idea get developed.

Timy2604
Frequent Visitor

May I know when MS plans to carry out this update? It's ....almost 4 years already. 

kengkreingkrai
New Member

Still no update for this

kcb4mk
New Member

Please let me know if there is any update. I am in March, 2022 but still see the same errors.

AnnaChu
Administrator
Status changed to: Completed
DavePotts_BritE
Advocate III

@AnnaChu my opinion is that you can't simply close this out with the work around as "implement a stored procedure in the database". What if you have access to a DB to run a SQL SELECT but no permissions to create a stored procedure - this is a *very* common scenario in my experience.

 

Plus it is a huge impediment to developing if for every new SELECT statement you have to implement a stored procedure. That's simply not how relational databases are designed to work.