A workaround for the 1000 row limitation on Active Directory Interface

While working on a simple system that would allow using Microsoft Word to mail merge documents to users on the network, I bumped into the proverbial 1000 row limitation imposed by the Active Directory interface.

My fancy solution involves creating a view out of a query which links SQL server to the Active Directory using linked server. Creating a linked server is another story entirely. Numerous tutorials exist on the Internet that show how to interlink all manners of data sources to Microsoft SQL Server.

Now, back to the issue at hand, the view in question shows only 1000 rows and in the organization where the solution is to be used, there are well over 2000 users on the Active Directory. Trolling the internet didn’t bring a respite as most workarounds required very long VB codes that are a mile from being elegant.

At the end of the day, I used a brute force; wrote a series of queries and union them together:

CREATE view ADInterface as

select * from openquery (
ADSI,’SELECT displayname,samaccountname,mail,employeeID
FROM ”LDAP://cve-hq-s001.dejiolowe.com”
WHERE objectCategory = ”Person” AND objectClass = ”user” AND employeeID > 0000 and employeeID<3000′)
union
select * from openquery (
ADSI,’SELECT displayname,samaccountname,mail,employeeID
FROM ”LDAP://cve-hq-s001.dejiolowe.com”
WHERE objectCategory = ”Person” AND objectClass = ”user” AND employeeID > 3001 and employeeID<4500′)
union
select * from openquery (
ADSI,’SELECT displayname,samaccountname,mail,employeeID
FROM ”LDAP://cve-hq-s001.dejiolowe.com”
WHERE objectCategory = ”Person” AND objectClass = ”user” AND employeeID > 4501 and employeeID<5500′)

Author: dejiolowe

Adédèjì is the founder of Lendsqr, the loan infrastructure fintech powering lenders at scale. Before this, he led Trium Limited, the corporate VC of the Coronation Group, which invested in Woven Finance, Sparkle Bank, Clane, and L1ght, amongst others. He has almost two decades of banking experience, including stints as the Divisional Head of Electronic Banking at Fidelity Bank Plc. He drove the turnaround of the bank’s digital business. He was previously responsible for United Bank for Africa Group’s payment card business across 19 countries. Alongside other industry veterans, he founded Open Banking Nigeria, the nonprofit driving the development and adoption of a common API standard for the Nigerian financial industry. Beyond open APIs, Adédèjì works deeply within the fintech ecosystem; he’s the board chairman at Paystack. Adédèjì is a renowned fintech pundit and has been blogging on technology and payments at dejiolowe.com since 2001.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.