Hi,

I am trying to query a list of 2500 users out of the AD in SQL Server with the following query:

SELECT employeeId, SAMAccountName, Mail
 FROM OPENQUERY( ADSI, 'SELECT Mail, SAMAccountName, employeeID FROM 'LDAP://dc=central,dc=mydomain,dc=int''WHERE objectCategory = ''Person''' )

However the result set seems to be limited to 1000 records. I have read that the "Page Size" parameter for AD should be given, but I don't know the exact SQL syntax.

... 'LDAP://dc=central,dc=mydomain,dc=int''WHERE objectCategory = ''Person'';''Page Size''=50' ) doesn't work. Neither does the range parameter.

Somebody suggested that I should partition the query, asking first for all userids starting with A, then B,... but I don't like that suggestion too much and even if I would use it, the Like 'A%' doesn't work.

Does somebody have any experience with this?

Many thanks,

Jan

Have a search on the site, as someone else asked the same question a week or so ago. Basically though, my understanding from the answers to that article was that the limit of 1000 records is set (hardcoded!) in AD and there is nothing you can do to change it ... therefore, paging your results was the only way to accomplish what you want!

Vinny is right. ADSI is limited to returning the first 1000 records without paging. Here's a Microsoft article for reference:

http://msdn.microsoft.com/library/en-us/adsi/adsi/paging_with_idirectorysearch.asp?frame=true

Quote from article:

"Searches of the Active Directory performed without paging are limited to returning a maximum of the first 1000 records, so you must use a paged search if there is the possibility that the result set will contain more than 1000 items."

Vinny & Newbie,

Thanks, but I knew this. My question is: how can we do this paging from the OPENQUERY function?

In a vb script (or for that matter any language that uses ADO), we can write something like that:

 objCommand.CommandText = _
 "<LDAP://dc=Central,dc=mydomain,dc=int>;(objectCategory=user)" & _
 ";distinguishedName,Name,sAMAccountName,primaryGroupID,memberOf,canonicalName,Mail,telephoneNumber;subtree"
objCommand.Properties("Page Size") = 50

This tells AD to send batches with 50 records at one time. But where can I put this parameter in the SQL syntax? The following doesn't work:

SELECT employeeId, SAMAccountName, Mail
 FROM OPENQUERY( ADSI, 'SELECT Mail, SAMAccountName, employeeID FROM 'LDAP://dc=central,dc=mydomain,dc=int''WHERE objectCategory = ''Person'';''Page Size''=50' ) 

I can partition (not page!) the query like this:

... FROM OPENQUERY( ADSI, 'SELECT Mail, SAMAccountName, employeeID FROM ''LDAP://dc=central,dc=mydomain,dc=int''WHERE objectCategory = ''Person'' AND SAMAccountName = ''A*''' )

and ask for A,B,C, ... , Z. But I prefer the paging attribute, and would like to know how to submit it.

Many thanks,

Jan

By the way, I ended up with the following code. Thanks for helping.

Jan

CREATE TABLE #tmpADUsers
 (  employeeId varchar(10) NULL,
  SAMAccountName varchar(255) NOT NULL,
  email  varchar(255) NULL)
GO

/* AD is limited to send 1000 records in one batch. In an ADO interface you can define this batch size, not in OPENQUERY.
Because of this limitation, we just loop through the alphabet.
*/

DECLARE @cmdstr varchar(255)
DECLARE @nAsciiValue smallint
DECLARE @sChar char(1)

SELECT @nAsciiValue = 65

WHILE @nAsciiValue < 91
 BEGIN

  SELECT @sChar=  CHAR(@nAsciiValue)

  EXEC master..xp_sprintf @cmdstr OUTPUT, 'SELECT employeeId, SAMAccountName, Mail FROM OPENQUERY( ADSI, ''SELECT Mail, SAMAccountName, employeeID FROM ''''LDAP://dc=central,dc=mydomain,dc=int''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )', @sChar
        
  INSERT #tmpADUsers
  EXEC( @cmdstr )
  
  SELECT @nAsciiValue = @nAsciiValue + 1
 END

DROP TABLE #tmpADUsers

Jan - I misunderstood your original question. Kudos on the work-around and thank you for posting it on the forum.

 I did, however,find a Microsoft article (Article 299410) that specifically addresses the paging issue within a distributed query. You must use other means to retrieve more than 1000 objects from ADSI. Quote:

"While paging is available through ADSI's OLEDB provider, there is currently no way available to perform it from a SQL distributed query. This means that the total number of objects that can be returned for a query is the server limit. In the Windows 2000 Active Directory, the default server limit is 1,000 objects."

http://support.microsoft.com/default.aspx?scid=kb;en-us;299410

Don