Hi.
We've been going crazy with an issue in a Power Automate flow that connects to an on-premise SQL Server via the gateway. And with "we" I also mean Microsoft support, with whom we have an open ticket for 2 weeks now and they're still "working hard to research the issue" 😞
The context is: we use Get Rows (V2) to call a database view, which is very simple and returns a very small dataset (about 900 records, in total a JSON result of 1.5mb... when we do get the result).
As I mention, this size is minuscule by all standards, but here is the issue -> randomly (as in sometimes it happens, sometimes it doesn't) the action fails with the following error: "The response is not a valid paginated response. The paginated response should not have the same non-empty 'nextLink' or 'odata.nextLink' property from the previous page when there are no items in the 'value' property". More context here:
Finally, we turned to the gateway logs. In there, we found what could actually be the real reason for the failures, which had nothing to do with the error message = timeout errors, with a total execution for the DirectQuery entries of over the 110 seconds of hard-coded limit. So the next question is: really? What is the gateway doing that it takes all that time to send through such a small dataset? Here is the only information we can find somewhat useful from the log's error entries:
Thanks for anyone who's managed to read this far, and we would hugely appreciate any insights into this.
Reply to self & to others who may arrive here.
(Very) long story short, as of this writing Microsoft support is still working on this ticket (yeah, a month and a half and counting 😱). We obviously had to move on with our own research, and essentially cleansed the server where the gateway was installed to give it more resources... and this fixed "everything". Well, at least we didn't run into the timeout and the flow works just fine, but the underlying errors (mainly in the exception handling from Power Automate) will surely still be there.
So the learning for others may be: if you get that error ("The response is not a valid paginated response. The paginated response should not have the same non-empty 'nextLink' or 'odata.nextLink' property from the previous page when there are no items in the 'value' property") from the Get Rows (V2) action of the SQL Server (on-premise) connector, your issue may have nothing to do with what the error is saying -> look at the gateway logs and, if you find timeout errors, work on giving the server where the gateway is higher capacity (CPU, memory or both).
Michael E. Gernaey
4
Super User 2025 Season 1
VictorIvanidze
2
GI-06050833-0
2