I am able to connect to my SQL Server using On Premise gateway connector in Power Automate(MS Flow).#sqlserver #MSFlow
But when I try to execute action "Execute SQL Query (V2)" from SQL server connector to run simple query "EXEC * from TableName" shows error "Gateway GetPassThroughNativeQueryMetadataAsync - this operation (Get Native Sql Metadata) is currently not supported using an on-prem gateway connection".
Please see the below screenshot:
Yes that's correct. Unfortunately Execute a SQL query not supported an on-prem gateway connection.
You can post this as an idea for future release.
I just started using Flow and ran into this issue. Is there a work around?
Flow lets me click ellipse and add data gateway and error goes away...but then I get error when I run it.
If somebody is looking for this , I resolved with the "transform data query editor" action . In my case I needed a parameter , if you need create any and get from the previous action .
This is probably one of the biggest use-cases in my opinion for custom logic especially where more than one table is involved in a query, and I keep hearing Power Query as a solution to this, however I have not found a clear enough example yet...
After searching everywhere and trying multiple things, I finally found how to proceed.
I posted everything you need to know on my blog: https://blog.kodono.info/wordpress/2022/12/09/power-automate-execute-a-sql-query-via-on-promise-gate...