Issues with Get Rows (V2) [SQL Server connector] via on-prem gateway

[juanblanco]

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:

1. In principle, my understanding is that we wouldn't need pagination due to the very small result size (isn't the default page size 2000k records or so?). However, without pagination the action returns quick enough... with only 54 rows. Why that precise number? Where could that be configured?
2. Since we obviously need a bit more than 54 rows, we turned pagination on, a large threshold (5000), etc. And as I mention, sometimes the action returns successfully, others it doesn't (with the above error). We have searched long for what that error could be about, but can't really understand what would be wrong (nor did Microsoft support). Have also tried all, really ALL variations of pagination thresholds, async yes or no, etc.

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:

• QueryExecutionDuration(ms) - In the 300 to 500 range, so the query is obviously flying (as we can test in SQL Server itself)
• DataReadingAndSerializationDuration(ms) - This is what is going over the threshold! But... why? And what can we do to avoid it (without breaking up an already tiny dataset into even smaller pieces)?

Thanks for anyone who's managed to read this far, and we would hugely appreciate any insights into this.

[juanblanco · Accepted solution]

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).