• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1302
  • Last Modified:

Crystal reporting on active directory and page size limits


I am trying to develop a reporting solution for our very large active directory infrastructure using Crystal Reports Dev 8.5.  We have hundreds of thousands of objects in our Active directory and currently no means to report against it.

Using Crystal 8.5 Dev I have managed to connect to the active directory using the below logic.

ADO data connection using ADSDSOObject as the server and the following ldap query as the recorset that is fetched.  

SELECT name, adspath, givenName, Department, EmployeeID, HomeDirectory, cn, displayName, middleName, initials, company, title, department, streetAddress, postOfficeBox, l, st, postalCode, c, mail, otherMailbox, telephoneNumber, homePhone, mobile, pager, facsimileTelephoneNumber, wWWHomePage from 'LDAP://DC=Domain,DC=com' WHERE objectClass = 'user'

This allows me to pull in records up to 1000 total then it stops.  I suspect this is related to a maximum page size limit set on our domain controllers.  I see the same limit on a linked SQL server I have also set up.

My question is...

Is it possible through this or a similar mechanism to specify a page size in the ldap string, or some other way to return more than the current 1000 rows I am seeing?

Am I chasing a dead end?

1 Solution
This is a limit of the ADsDSOObject provider and is hard coded at 1000.  You cannot modify this limit in your query string, nor is there a registry setting or an ini file setting to change it.  There are two workarounds:

1.  Limit your query to a desired working set with a where clause

2.  Use multiple select statements and union them together.  This may require that you create a stored procedure on a SQL Server and base your report off that stored procedure.

I believe the main reason for the 1000 record limit is that large queries against the AD data store will either have a huge impact on domain contoller performance, or more likely, will blue screen the server you are querying.  


(All right, the last part about the blue screen isn't really the reason, but the performance hit is).
aliscoolAuthor Commented:
Thank you DRRYAN3,

I am rethinking the whole thing now, I have built some vb scripts that will enumerate the Active directory one class at a time... at first they were dumping to excel but now that we have grown past the row limit of excel for most I am dumping them as text then importing to SQL.  Still really clunky though for our production enviroment.

If I figure out a way to import to SQL directly or a work around with Crystal I'll post for all to see.

again thanks for your time,
According to the following article from Microsoft, you can exceed the 1000 record limit on reading Active Directory Data by setting the Page Size parameter to some value I.E 100.  This enables Client/Server paging.  

The problem I'm having is I cant find anywhere in the Crystal Report OLE DB provider for Directory services to set this parameter. Im using Crystal Reports Developer 8.0


Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now