Crystal reporting on active directory and page size limits

Posted on 2003-03-12
Medium Priority
Last Modified: 2008-02-26

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?

Question by:aliscool
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 12

Accepted Solution

DRRYAN3 earned 300 total points
ID: 8119790
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).

Author Comment

ID: 8121450
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,

Expert Comment

ID: 9408626
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question