REST API to external list not working consistently (SP 2019 on-premises)

%3CLINGO-SUB%20id%3D%22lingo-sub-1994414%22%20slang%3D%22en-US%22%3EREST%20API%20to%20external%20list%20not%20working%20consistently%20(SP%202019%20on-premises)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1994414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20external%20content%20types%2C%20which%20are%20read%20operations%20(item%2Flist)%20to%20SQL%20Server%20views.%20I've%20made%20external%20lists%20from%20them.%20There%20are%20hundreds%20of%20thousands%20of%20entries%20in%20the%20table%2C%20so%20I've%20increased%20the%20return%20limit%20from%202000%20to%2010000.%20Besides%20that%2C%20I%20have%20limit%20filter%20to%2010000%20entries%20in%20my%20CTs.%20The%20problem%20is%20that%20whenever%20I%20send%20query%20with%20filter%20on%20date%20field%2C%20it%20looks%20like%20there's%20some%20sort%20of%20limit%20of%20what%20I'm%20getting.%20I%20thought%20that%20the%20external%20list%20limit%20is%20for%20maximum%20amount%20of%20entries%20returned%20from%20single%20operation%2C%20but%20I'm%20not%20sure%20now.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20instance%2C%20I%20have%20results%20for%20this%20date%20from%20Management%20Studio%3A%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221MFM6.png%22%20style%3D%22width%3A%20618px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F241130i32809F9472E7868C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221MFM6.png%22%20alt%3D%221MFM6.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20when%20I%20make%20a%20query%20to%20my%20external%20list%20based%20on%20this%20view%3A%3C%2FP%3E%3CPRE%3E_api%2Fweb%2Flists%2Fgetbytitle(%3CSPAN%20class%3D%22hljs-string%22%3E'Bnp55All'%3C%2FSPAN%3E)%2Fitems%3F%24select%3DID%2CData%26amp%3B%24top%3D%3CSPAN%20class%3D%22hljs-number%22%3E1000%3C%2FSPAN%3E%26amp%3B%24orderby%3DID%2520desc%26amp%3B%24filter%3DData%2520le%2520datetime%25272020-%3CSPAN%20class%3D%22hljs-number%22%3E11%3C%2FSPAN%3E-%3CSPAN%20class%3D%22hljs-number%22%3E12%3C%2FSPAN%3ET11%3A%3CSPAN%20class%3D%22hljs-number%22%3E00%3C%2FSPAN%3E%3A%3CSPAN%20class%3D%22hljs-number%22%3E00%3C%2FSPAN%3E.%3CSPAN%20class%3D%22hljs-number%22%3E000%3C%2FSPAN%3EZ%2527%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3CSPAN%3EI'm%20not%20getting%20any%20results.%20If%20I%20change%20the%20'Data'%20field%20filter%20to%202020-11-14%2C%20I'm%20getting%20those%20results%20from%20the%2011-13%20day.%20Now%2C%20does%20this%20mean%20that%20ECT%20limit%20impacts%20returned%20results%2C%20and%20I%20can't%20get%20results%20in%20any%20way%20some%20time%20back%3F%20Is%20there%20a%20way%20to%20overcome%20this%2C%20so%20I%20could%20just%20filter%20on%20the%20date%20field%20without%20restrictions%20(for%20instance%2C%20get%20records%20from%20last%20year)%3F%20Or%20is%20it%20something%20else%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have external content types, which are read operations (item/list) to SQL Server views. I've made external lists from them. There are hundreds of thousands of entries in the table, so I've increased the return limit from 2000 to 10000. Besides that, I have limit filter to 10000 entries in my CTs. The problem is that whenever I send query with filter on date field, it looks like there's some sort of limit of what I'm getting. I thought that the external list limit is for maximum amount of entries returned from single operation, but I'm not sure now.

For instance, I have results for this date from Management Studio:

 

1MFM6.png

 

But when I make a query to my external list based on this view:

_api/web/lists/getbytitle('Bnp55All')/items?$select=ID,Data&$top=1000&$orderby=ID%20desc&$filter=Data%20le%20datetime%272020-11-12T11:00:00.000Z%27

 I'm not getting any results. If I change the 'Data' field filter to 2020-11-14, I'm getting those results from the 11-13 day (but not 11-12). Now, does this mean that ECT limit impacts returned results, and I can't get results in any way some time back? Is there a way to overcome this, so I could just filter on the date field without restrictions (for instance, get records from last year)? Or is it something else?

0 Replies