MarkMpn
commented
3 months ago Because this filter cannot be folded into the FetchXML, SQL 4 CDS will retrieve pages of data until it finds 10 that match the criteria. Depending on your data it may need to retrieve a lot of results before it finds 10 that match. You may want to apply some additional criteria such as a filter on createdon to reduce the amount of data it will consider.
On my test system I get this same error because there are no records that match the filter within the data. If I change my filter to values that do occur in my data, e.g. looking for a statecode of 1, I get the expected 10 results even though it had to retrieve over 13,000 records to find those 10.
edmarveras
when I try to query the Audit table and use the changedata in the where condition the execution ignores the TOP X restriction trying to read all the rows.
Example SELECT TOP 10 objectid, objectidname, action, actionname, changedata, createdon FROM audit WHERE action = 2 AND JSON_VALUE(changedata, '$.changedAttributes[0].logicalName') = 'statuscode' AND JSON_VALUE(changedata, '$.changedAttributes[0].newValue') = '5'
Error Message: Msg 10337, Level 16, State 1, Line 14 Hit maximum retrieval limit. This limit is in place to protect against excessive API requests. Try restricting the data to retrieve with WHERE clauses or eliminating subqueries. Your limit of 100 retrievals per query can be modified in Settings.
See the Execution Plan tab for details of where this error occurred
Completion time: 2024-05-29T18:35:06.6063588-03:00