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
Gr8Britton
Frequent Visitor

@AnnaChu 

 

As @DavePotts_BritE stated, you cannot close a ticket as resolved with a band-aid fix.  This is NOT a FIX for this issue.  We need to be able to perform a Select, not just Stored Procedures.

CromeSchool
Regular Visitor

Hi @AnnaChu 

I agree with the previous comments.  This idea shouldn't be closed out.  The proposed workaround is not sufficient for various reasons.  This is a low code platform that shouldn't rely on having to create stored procedures in order to achieve an outcome.

Power BI allows SQL queries to databases through an on premise gateway, so why doesn't Power Automate?  This functionality should be available across the entire power platform.

Can you please re-open this idea?

Thanks

Steve

dynamicsgeek
Frequent Visitor

Hi since this thread is getting longer and it might get lost, I wanted to re-iterate the @gpohlod workaround for this issue posted back in 2021. It continues to work for me and I have utilized it many times to execute a sql query against a on-premise database in power automate flow via on-premise gateway.

 

It is currently discussed in detail on page 4 of these comments, but the short explanation- use Power Query connector and back out the applied steps selection of the database table and replace it with your query and database name.

CromeSchool
Regular Visitor

Thanks @dynamicsgeek,  I'm glad that work around works for you, but it doesn't work for me or a lot of other users.  See outstanding bug - https://powerusers.microsoft.com/t5/I-Found-A-Bug/Transform-data-using-Power-Query-Create-query-butt...