When using OLE DB Provider for Microsoft Active Directory Service to fetch user information from Active Directory into a QlikView/ Qlik Sense table, the reload fails with error "OLEDB read failed"
The screenshots below are from QlikView but the scripting would work with Qlik Sense as well when working with the script
Environment:
QlikView
According to Microsoft documentation at https://docs.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-ole-db-provider-for-microsoft-ac..., OLE DB Provider for Microsoft Active Directory Service is based on the Active Directory Service Interfaces (ADSI). Also according to the Microsoft library page at https://msdn.microsoft.com/en-us/library/aa746414(v=vs.85).aspx, ADSI searches without paging are limited to returning a maximum of the first 1000 records. This is therefore a known limitation of the OLE DB provider.
Note that although the documentation says the limit is 1000 users, the reload may still fail with "FIRST 1000" in the QlikView/ Qlik Sense Load statement. However, the reload works with "FIRST 999". Since the loop eventually loads all users, the exact limitation shouldn't be a problem.
Resolution:
OLE DB Provider for Microsoft Active Directory Service can only load maximum 1000 users each single query. If you have more than 1000 AD users, use a loop to load at most 999 users at a time. See the screenshot below for a demonstration.
The sample application can be found in the following Community discussion: https://community.qlik.com/thread/98001