Stored Proc returns ? for Result Set from SQL Server

(0) Share

Report

Posted on by GBM31

I have a Power App with two Premium SQL Connectors in it:

Azure SQL Database
SQL Server 2019 Database, running on an Azure VM

I set both up for stored procedures, passing in parameters, and allowing data to be shown in galleries.

I am using buttons to call the procedures and store the returned data in Collections, using the ClearCollect command.

The first (Azure SQL Database) works just fine. I can see the data in Collections. It gives me a little, clickable table icon under ResultSets, and I can manifest the collection in a gallery nested inside another gallery.

The second (calling SQL Server based on VM, using an On Premises gateway) does not work. Under Variables > Collections it says "Table: 1 rows" but when rClick to view the table, the ResultSet for the collection shows a ? question mark instead of a clickable table icon.

How do I get it to show data?

FYI, when I set it up, it allowed me to see a list of the stored procedures in the database, and when I code a button to call it, the parameters for the stored procedure show in the prompt. But when I click the button, passing in a valid parameter (which works when running from SSMS), only the ? is returned in the ResultSet.

It should look like this:

But instead it looks like this:

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (2)

Answers (1)

Suggested answer

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (0)

Report

Here are my thoughts:

Power Apps expects stored procedures to return a single result set for data-binding purposes. Ensure:
Check the Data Gateway Configuration
Verify Data Source Configuration:
When calling the stored procedure, ensure that the result set is captured properly into the collection.
ClearCollect(
    colResults,
    '[dbo].[GetData]'(
        Parameter: 123 // Replace with your parameter value
    )
);

Was this reply helpful? Yes No
Verified answer

MB-23070938-0 48 on at

Like (0)

Report

You need to specify each column and their data type to get the table to show up.

https://learn.microsoft.com/en-us/power-apps/maker/canvas-apps/connections/sql-connection-view-results

Set(
<MyUntypedObject>, // pull results into variable
<datasourceName>.<StoredprocedureName>(
{ <paramName1>: "someString" }
).ResultSets
);
Set(
table1, // put Table1 into table1
<MyUntypedObject>.Table1
);
Set(
TypedTable,
ForAll(
table1, // extract JSON from table1
{
BookID: Value(ThisRecord.BookID),
BookName: Text(ThisRecord.BookName)
}
)
);

Best

M

Was this reply helpful? Yes No