You’re offline. This is a read only version of the page.
In SQL I have a table with a start date. I can order by that and see below:
In power automate I have a get rows order by same column.
But no matter what I change it never returns all the same rows??
I can't see the reason? I'm not filtering. Pagination is set to on and 100000. This order by should return same records right??
So for example look at row 2 from SQL... start date 2023-05-30, but if you look at the returned data in Power automate, no start date 2023-05-30?? there's several other rows missing too?
Anything else I should do?
Thanks,
Terry
This may not be the cause of the OP issue but I found this post by subject. I had the problem as described in the subject Get Rows V2 was returning an seemingly arbitrary and small amount of rows despite the actual row count being much higher.
It turns out Get Rows has a hidden limit of raw data returned, not just a row count limit. In my case I had a couple of columns with varchar(MAX) that had huge amounts of data in a couple rows. When Get Rows retrieved them, it hit this hidden limit and started returning far less rows than the limit I specified. In my case it was as simple as excluding those columns as I didn't need them. You could do this by setting a Select Query in the advanced options of Get Rows v2 excluding the columns with too much data if you don't need them. I suppose in general we should be doing this anyway but I know in practice we often leave it to the default which is select * basically.
Essentially, if you are getting less rows than you expect, check your data and make sure you don't have any columns with (max) and lots of data in them.
The point of the note is that sorting by something non-determinative can cause issues when using pagination. Duplicates and missing rows could easily be caused by exactly the same thing. My point is that in the previous version of this action using Dates was a non-delegable function. Its supposed to be delegable in this version for sorting. But I would test it with some other field to see if sorting on Date is what is causing the problem.
There's not really a primary key, the main field is a guid. So not sequential.
But even so that article above talks about getting duplicates... I'm getting the opposite. Missing rows.
And it seems to be the same rows... I think. So what would be different?
Terry
I would try doing the Get Rows using a primary key in the Order By field or something other than a date. I know Order By works in the SQL Query, but the connector doesn't do things exactly like a raw SQL query and there are some notes in the connector documentation about issues using the Order By field with non-deterministic data. I know there were delegation issues in the past with the SQL connector. I suspect Order By and pagination is what is causing your issue.
Usage of the Order By parameter is recommended in order to get deterministic results in action output. If Order By isn't specified, primary keys or unique keys are used by SQL Server by default. Non-deterministic results might cause issues, such as duplicating records in the action output when pagination is enabled. SQL views don't support primary key, which is the limitation from SQL Server itself.
#1
stampcoin
61
#2
Michael E. Gernaey
47
Super User 2025 Season 1
#3
rzaneti
31
Super User 2025 Season 1
Share
Report
All responses (
Answers (
