Answered

Using query with on-premise gateway

(1) Share

Report

Posted on by JazzBird

I am getting an error just clicking in the query section when using an on-premise gateway. How or where do construct a query in Power Automate to send the information back to Power Apps?

Categories:

Using connectors

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Michael E. Gernaey 53,928 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @JH-22040758-2

So the error you are getting there, isn't related to sending data back, its simply doing something that isn't supported.

If you want to send data back to a Canvas App or Model-Driven App, you must take the data you get from (whatever actions you do) and (the best way) is to form is into a JSON format.

Then use the Response to a Power App action, and send it back via an outgoing string property.

Then inside the App use the ParseJSON function (or depending on what you did back parse somehow) the data you want into something you can use.

The above though isn't related to passing back data, its related to making an unsupported call.

So make a copy to SQL

Get your Data

Use the Data to build a JSON structure

Pass it back as a string in an Output parameter

Reconstruct it into a Collection (aka JSON) in the App

Was this reply helpful? Yes No
Suggested answer

PS-28031948-0 4 on at

Like (0)

Report
Copy link

Link copied!

This is known issue and limitation in Power Automate. The error message has the reason - "Execute Native Sql is currently not supported using an on-prem gateway connection".

Please refer to SQL Server - Connectors | Microsoft Learn Section: Known issues and limitations with actions.

Workaround: Instead of using "Execute a SQL Query (V2)" and writing SQL, I used "Get rows (V2)" option and hit the view (If the query is complex - create it as view). While querying the view you may use filter option with 'OData' syntax. Provided screenshot for reference.

Thats the work around I used. This is just a workaround to handle the current limitation.

Was this reply helpful? Yes No