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: New
Comments
Super User II

Hi everyone

 

Below are two videos dealing with advanced queries to SQL from PowerApps (using Flow).
For SQL Azure you can use direct (native) queries:
https://youtu.be/DII10gK715I

 

For on-premesis SQL servers, native queries are not supported from Flow (you will get an error: operation Execute Native Sql is currently not supported using an on-prem gateway connection), so we have to use stored procedures to accomplish the same:
https://youtu.be/BAGBzI4zdww

 

Please let me know if you don't come right.

 

Dawid van Heerden
Follow on Twitter: @davestechtips
Subscribe to YouTube: https://www.youtube.com/davestechtips?sub_confirmation=1
**If you found this reply helpful, please mark this as the answer to close the topic and make it easier to find for other people with similar questions.

Regular Visitor

@DavesTechTips  Great videos - any chance you have seen a similar workaround for MySQL?

Regular Visitor

thanks dave,  please microsoft implement this feature for on premise!!!

We should make a movement!!!!

Frequent Visitor

It's 2020 and still no news for this? 

Regular Visitor

This would be extremely helpful

Regular Visitor

Ok if I may ask respectfully, this approach has helped me before when working with Microsoft products, what CAN "Get Rows" do then, if anything using on-prem gateway without using SPs? Anything? 

 

I read earlier in this post that "The "Get rows" action allows users to pull records from a single table" but that also appears to be false or is there truly a way to get records even from a single table WITHOUT having to use SP?

 

Ok let's change gears and seek some kind of a work around. PowerBI data sets do not have this defect, I can run queries against any SQL server via on-prem gateway. Has anyone been able to query a PowerBI data set from Flow with some kind of mechanism which allows arbitrary SQL query, just the source would be PowerBI.

The theory is that we use PowerBI as a spring board to go around this defect with Flow? If anyone has done it please share the "how" - in summary, Flow uses "some kind of connector" to execute a query against a PowerBI data set. The data set is populated by PowerBI with whatever queries are needed and executed against on-prem SQL server. Ping-Pong 🙂

 

Thank you

~B

New Member

I'm busy selling the idea of M365 to our hospital and I wanted to demonstrate the integration with our on-premises HR system for days-off request and there, I face this apparently old issue that stops my progression. the work around is to develop REST Api to query my DB but please MS, make an effort on this. Also the substringof function is not yet supported in the get rows v2 ???

 

Regular Visitor

@StephFos Well if you follow Microsoft's lead you would write bunch of SPs on your on-prem SQL and execute them from Flow, there you can do any sub-string manipulation you want but of course it won't be optimal. I can't do SPs in my environment as the SQL box is vendor no-touchy hence my post and my overall sad situation of being stuck here...