Skip to main content

Notifications

Power Automate - Using Connectors
Unanswered

Not all records loading a sql database table in a flow using get rows v2

(0) ShareShare
ReportReport
Posted on by 628

Powerapps user occasionally using a flow.

Made flows loading dataverse tables no problem.

I started fidling with sql connector and discovered I cannot use sql query v2 (server will not allow) but I can use sql get rows.

Basically it works, added a response step which results in a body like this

 

 

 

 

{
 "@odata.context": "some_url",
 "value": [
 {
 "@odata.etag": "",
 "ItemInternalId": "4782b71d-e9cb-4114-80e9-53d48683aef0",
 "dier_id": 2,
 "diersoort_id": 1
 },


..


 {
 "@odata.etag": "",
 "ItemInternalId": "909d33c4-c675-47ba-81d3-feb1fb22d9f0",
 "dier_id": 2048,
 "diersoort_id": 2
 },
 {
 "@odata.etag": "",
 "ItemInternalId": "5823fa2b-b67b-4b2f-a0df-bf60cba80bff",
 "dier_id": 2049,
 "diersoort_id": 2
 }
 ],
 "@odata.nextLink": "some_url"
}

 

 

 

 

.. is for all the record inbetween, some_url is some url

Some more fields then I requested but no problem.

Before I start worrying how to use this response in powerapps I am worried that the number of records when I test is way lower that the actual table has (bit over 2000 vs 50000 ish). I set no top count in the get rows step, just entered the two fields I wanted (dier_id an diersoort_id)
Any ideas why this might be?

 

 

>Edit: after some minor adjustments the flow perfectly loads selected fields into a powerapps collection but for only loading 2048 records whichs seems to be 2^11 somehow.

 

>Edit: by using "2048" as google leverage I found i can adjust the pagination property of the get rows step. If I set the treshold to some number I get some more than the number. If I set the treshold to 100000 (max) I get all 53779 records. I still wonder what happens if a table far exceeds 100000 records.

  • lbendlin Profile Picture
    lbendlin 7,604 on at
    Re: Not all records loading a sql database table in a flow using get rows v2

    Limits and configuration - Power Automate | Microsoft Learn 

     

    maybe the 100 MiB message size limit strikes again. one of the most infuriating limitations of Power Automate.

  • wskinnermctc Profile Picture
    wskinnermctc 6,512 on at
    Re: Not all records loading a sql database table in a flow using get rows v2

    If the table exceeds 100,000 rows the Get Rows V2 connector will not return more than 100,000 sortof.

     

    I did try this and it returned 100,352 rows but my sql table has over 500,000. So the 100,000 threshold is close enough. Maybe the type of data or size will change the amount over 100,000? 

  • lbendlin Profile Picture
    lbendlin 7,604 on at
    Re: Not all records loading a sql database table in a flow using get rows v2

    You will need to include the pagination logic in your flow. 

     

     discovered I cannot use sql query v2 (server will not allow)

    Not entirely the case. You can work around that by using stored procedures.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,567

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,907

Leaderboard

Featured topics