SQL Get Rows v2 not returning all rows...

(0) Share

Report

Posted on by Mattw112b

1,088

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.

Screenshot 2023-05-19 160457.png

But no matter what I change it never returns all the same rows??

Screenshot 2023-05-19 160609.png

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

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

SQL Server - Connectors | Microsoft Learn

Was this reply helpful? Yes No
Mattw112b 1,088 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Pstork1 68,707 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
DHepler 11 on at

Like (1)

Report

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.

Was this reply helpful? Yes No