Power Platform Community Forum Thread Details

I'm trying to use the Execute a SQL statement tool in a Copilot Studio agent. I get the following error when I try to use it:

Evaluating PowerFx expression resulted in error while invoking the connector : PVA.SqlExecuteStatement failed: PowerFxJsonException Expecting Record but received a String, in result/data_array Error code: ConnectorPowerFxError.

In the response body from Databricks, result.data_array is an array of arrays of strings, why is it expecting a record?

I've used the same tool with the same query in Power Automate and it works fine.

Categories:

Calling actions from Copilot Studio

This error occurs because Copilot Studio (Power Virtual Agents) expects the connector response to be an array of records (objects), not an array of arrays (lists of lists). PowerFx requires structured data (records with named fields) for parsing and binding.

Why does Power Automate work?
Power Automate is more flexible with data types and can handle arrays of arrays, but Copilot Studio/PowerFx expects a record structure for each row.

How to Fix?
Modify the connector or tool to return an array of records.

Instead of:
[
["value1", "value2"],
["value3", "value4"]
]
Return:
[
{ "col1": "value1", "col2": "value2" },
{ "col1": "value3", "col2": "value4" }
]

If you control the connector/tool:

Map the array of arrays to an array of objects before returning to Copilot Studio.
If you don’t control the connector:

Use a Power Automate flow or an intermediate step to transform the data into records before passing to Copilot Studio.