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
Regular Visitor

Depending on your need, you could use the transform data using power Query option. 

 

gpohlod_0-1607721205916.png

 

at first it forces you to choose a full table, but if you just pick one, then click transform:

gpohlod_1-1607721423268.png

 

delete the 2 navigation steps can click on the settings wheel by source:

 

gpohlod_2-1607721505296.png

 

I was then able to tell it what database to use and add my custom query that joined several tables.

gpohlod_3-1607721629421.png

 

create my csv table based on the query and go on my way... 

 

gpohlod_4-1607721662185.png

 

hope this helps for others here

 

Frequent Visitor

@gport That's awesome suggesiton - just to confirm, you were able to do this using the database gateway and run your custom query against an on-prem SQL server?

Regular Visitor

hey @Boyan yes we are using the database gateway and I was able to run the custom inner join query against our on prem SQL server.

 

you can also use the power query editor instead or in addition to your custom query once you update the source to use your query instead of just pulling the selected table.

 

It doesn't look like they quite made this option specifically for custom queries, but if you follow the screenshots from my other post you can definitely add it in and it worked for what I was trying to accomplish.

 

Frequent Visitor

@gpohlod Cool! Everyone I am confirming that the solution proposed by @gpohlod  works. Excellent work sir, thank you so very much!

Regular Visitor

@gpohlod 

This is the greatest workaround! You win the day, week, month, year!

 

And Microsoft - come on guys. I get it but I don't get it. I understand that executing native queries is a security issue and could alter the database but the likelihood of your average Power Automate user having access to create Stored Procedures on the database is very low. Most of us have read-only access and by relegating us to Stored Procedures, you have put the power back into the hands of DBAs. The fact that "Execute a SQL Query" runs against Azure gives the appearance to me of being convenient for your wallet or part of a push to make sure on-premise data moves to the cloud. As a Data Citizen, who is trying to sell my company on the capabilities of the cloud and your workflow automation, I am looking to demo this to my IT folks so they understand we have a stop-gap as we move everything to your cloud, which is still very much going to pad your wallets. Going to them now and asking to create a Stored Proc on the database is going to raise more questions than if I was to demo awesome workflow functionality using on-premise data sources. Let the Data Democracy rise!  

Microsoft

I reckon this feature is must. Please assit to add.  

Regular Visitor

Please select this idea and prioritise its development. We are blocked to automate some of our processes due to this limitation. We've tried to use other available methods (i.e. insert rows or run stored procedure) but due to the length of the transactional data, it's not possible. Looking forward to hearing back from Power Automate team at Microsoft.

Regular Visitor

@rlazaro  and @ajdhingr 

 

Would the transform using power query solution I posted on Dec 11th work for what you are trying to do?

 

It provided me the ability to run any query I needed to within power automate.

 

Greg

Regular Visitor

Hi Greg,

Unfortunately not. For some reason it's giving me an exception error "The on-premisses data gateway could not be found" therefore, the power query screen is not opening for me. I've already raised a ticket so hopefully it will be sorted soon. Really appreciated your suggestion!

 

However, I'm prototyping now using the stored procedure where it will receive some inputs and will execute some dynamic SQL.. so I guess this will work as well. Fingers crossed.

 

Thanks for the follow up.

Cheers! 

 

Regular Visitor

We received that error initially as well. 

 

First it was because our on premises gateway was set up for power bi only. We set it up instead to also include powerapps/power automate. But were still having the issue.

 

The next fix was that we discovered our power bi data is stored on a different microsoft server than our power automate/power apps. (one was on toronto canada servers, the others were in the US). and the on premises gateway can only be set up for 1 server location. So we created a new power automate environment using the same server location as our power BI data. Then the on premises gateway was found and could use the query.

 

Hope that helps