The cap of 20 records is real and unfortunately as of today it isn't configurable. If you ask for more, you get a hard error rather than a partial result:
SELECT TOP 25 name FROM account
-- Requested TOP 25 exceeds the maximum of 20 records.
Although, there's a potential solution that can help you.
The thing that made it click for me: the cap is on the rows that come back, not on the rows the query looks at. The filtering and the maths still happen server side across the whole table. So if you shape your query so the answer is small, the cap never touches you.
Example of how I made it work:
Use-case: I wanna know how many active accounts we have, and how they split by status.
Instead of pulling the records and counting them myself, I let Dataverse do the counting:
SELECT COUNT(accountid) AS total FROM account WHERE statecode = 0
That comes back as a single row with the correct number, even when the table has thousands of rows. Same for a breakdown:
SELECT statecode, COUNT(accountid) AS c FROM account GROUP BY statecode
Two rows out, both correct. The cap is irrelevant because the answer was only ever going to be small.
One trap worth knowing about, because it caught me out. The 20 row cap applies to grouped rows too. I ran a GROUP BY on a date column and got back 20 groups that all happened to be the oldest ones in the table. No error, no warning. It just looked like a complete answer and wasn't. So GROUP BY is safe when the column has few distinct values (status, type, owner). It quietly lies when the column has many (dates, names, IDs). If you must group on something high cardinality, add an ORDER BY so at least you control which 20 you see.
If you genuinely need the full list, not an answer, there's no OFFSET so you can't page through it. What worked for me was windowing:
- Run a COUNT first so you know the real total.
- Pull 20 with an ORDER BY on a date column.
- Note the oldest date you got back, then run again with that date as the upper bound.
- Repeat until the rows you've collected add up to the COUNT.
The COUNT is the important bit. It's what tells you when you're actually finished, instead of guessing.
Honestly though, for most agent scenarios I'd avoid step 2 onward entirely. If you're asking an agent a question, let SQL do the aggregation and return a small answer. The 20 row cap only really hurts when you're using the MCP server as a data extractor rather than a question answerer.
Hope that helps. 👍🏻
AI-Assisted: I have used AI to run Dataverse MCP against queries for retrieving records and validate the results. Also, simply formatted my message with AI.
