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′)

Post a Comment

Your email is never published nor shared. Required fields are marked *