SQL Get Rows (V2) returning correct number of rows, but producing duplicates

[LewisPark101]

Hello all, 

I am in the process of building a flow that aims to achieve the following -

1. Retrieve a dataset from on-prem SQL Server (around 4000-500 rows)

2. Select this into JSON

3. Pass the JSON to Excel to write out to a table before emailing the file to the user

We have a primary key on the source table, with a view sitting on top. We query the view from the get rows function.  Now, when querying the view via SSMS, we get 4018 distinct records, since the primary key is enforced. When the flow runs, we get 4018 records returned also. Looking at the output of the Get Rows function, it appears that we are dropping some rows, and picking up duplicates of other rows instead. 

I did previously view this post - https://powerusers.microsoft.com/t5/Building-Flows/SQL-Get-Rows-is-producing-duplicates/m-p/582799#M...

However, in our case, the table has an index. In addition to this, we have included a sequential row id column to the view to then apply an order by in the Get Rows function, as per some of the known limitations for this connector. With all of the above, we still see the correct number of rows return, but with some rows dropped, and replaced by duplicates. 

Wondering if anyone has had any luck resolving the issue? 

For reference, this is the step in the flow - 

The 'WhereClause' variable simply holds an Odata query stating - Fieldname eq variablename

Settings on this step -

Note: We have tried with the threshold at both higher and lower numbers. 

Any help would be greatly appreciated

[v-jefferni]

Hi @LewisPark101 ,

Have you tried get rows from the table directly other than the view?

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[LewisPark101]

Hello, 

We have not, since the view handles the relationship between our main data table, and the dimensions associated with the items. Therefore, querying the table directly would give us only the numeric keys underpinning the structure of the export. 

We have queried the view directly in SSMS and get the correct result - it appears the issue is only when data is being fetched from the same view via the Get Rows function. 

Thanks, 

Lewis 

[lebarb74]

I am having the same issue. We cannot access the table directly. Only the view is exposed to us. We have verified that the records in the view are not duplicated. Is there a resolution to this issue? Thanks.

[wskinnermctc]

I'm doing something similar with a SQL Get Rows(V2) loop to get all of the items. I'm now searching for a way to confirm all of the rows are pulled after all of the loops.

In my flow I'm using a length count of items to skip in each loop, but that doesn't guarantee the SQL View will return the items in the same order each time.

My main concern (which I think might be impacting the scenario above) is that an SQL View is different than an SQL Table. An SQL View can change the sort order each time it is pinged since a View does not have an order and SQL is free to return the rows in the most efficient way, which won't necessarily match the order you expect. An SQL View is different than a table where the view only exists if you're looking at it (with a query) and an SQL Table has items in an order stored at all times looking at it or not.

Example of a SQL View might contain all items below:

Item Unique ID

A99

B88

C77

D66

E55

F44

G33

H22

J11

I can make a flow that has a loop to get 3 items each time and puts them all together in a holding array until I have 3 loops that make 9 total items.

I can run the flow and the first loop could return (A99, B88, C77), but that doesn't guarantee the next loop will return (D66, E55, F44) since each loop is basically a new view table. If the SQL decides to order the view differently than the first example table, it would pull the next 3 for whatever order the View is in which could lead to duplicates.

Example of a SQL View loop 1 could be below with the first three items (A99, B88, C77)

Item Unique ID	Row Count
A99	1
B88	2
C77	3
D66	4
E55	5
F44	6
G33	7
H22	8
J11	9

Example of a SQL View loop 2 could be below with the next 4,5,6 items as (C77, E55, F44): 

Item Unique ID	Row Count
H22	1
B88	2
D66	3
C77	4
E55	5
F44	6
G33	7
A99	8
J11	9

Example of a SQL View loop 3 could be below with the last 7,8,9 items as (F44, A99, B88): 

Item Unique ID	Row Count
H22	1
J11	2
D66	3
C77	4
E55	5
G33	6
F44	7
A99	8
B88	9

If I put all of my loop results in an Array I will get (A99, B88, C77, C77, E55, F44, F44, A99, B88) which is 9 items but obviously not all unique items.

Even with the flow having an Order By in the Get Rows action, that is after the view is generated. So if the view is generated in a different order than the first loop, the Order By in the action doesn't necessarily solve the problem.

I can't confirm a loop is exactly the same as pagination, but I have had issues with it similarly. This really isn't an issue at small Views since it can get all items at once without pagination. But if the View is larger and needs pagination or loops then you should have a mechanism to help offset the fact that a SQL View order can change every time you look at it.

The link below is not saying that a flow can't order the results of a view, (a flow can absolutely Order By the results of a view), but it is saying that a View cannot be created with an Order By. Therefore, just pinging a view with a flow doesn't guarantee an order since it doesn't exist with one. 

Learn Microsoft Order By Clause SQL View 

[wskinnermctc]

Upon further looking it is confirmed on Microsoft webpage discussing SQL Server connector.

Microsoft SQL Server Connectors - Known Issues and Limitations 

GET ROWS (V2) - 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.

Microsoft Deterministic and NonDeterministic Functions 

So have to find a work around to confirm you have all items from the view.

[Dan007]

Thank you so much, we wasted 4h trying to figure this out!