SQL ADSI Interface limitation

Question

Post reply

SQL ADSI Interface limitation

Jan-155192

SSCrazy

Points: 2613
More actions
October 24, 2005 at 8:12 am

#166016

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

mccork SSC-Insane Points: 22288 More actions · Answer 1

take a look at:

http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx

Cheers,
- Mark

David Burrows SSC Guru Points: 65143 More actions · Answer 2

Somebody suggested that I should partition the query

Using linked server, that is your only choice. Try changing the LDAP query

'<;(&(objectCategory=person)(SAMAccountName=a*));employeeId,SAMAccountName,Mail;subtree'">LDAP://DC=central,DC=mydomain,DC=int>;(&(objectCategory=person)(SAMAccountName=a*));employeeId,SAMAccountName,Mail;subtree'

etc

Far away is close at hand in the images of elsewhere.
Anon.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Mark,

the link you posted

http://www.microsoft.com/technet/scriptcenter/resources/qanda/aug04/hey0824.mspx

is so cool. It is working well for me. I just replaced DC parameters with our domain names.

I also use dsget and dsquery from the command prompt. You should run it from a server machine. Something like:

dsquery user ou=InnerMostOU,ou=NextUpperOU,ou=UpperMostOU,dc=InnerDomainName,dc=UpperDomainName,dc=MyCompanyName,dc=com -o upn -limit 2000

To get a list of dsquery commands do 'dsquery /?' To get a list of dsquery user command parameters do 'dsquery user /?' (all without quotes) -limit parameter is explained as:

-limit <NumObjects> Specifies the number of objects matching the

given criteria to be returned, where <NumObjects>

is the number of objects to be returned.

If the value of <NumObjects> is 0, all

matching objects are returned. If this parameter

is not specified, by default the first

100 results are displayed.

Can someone tell me how to create a linked server to Active Directory? There already were several discussions here and I looked up online topics about ,ADSDSOObject and can not figure out how to create a provider string. I do know and use other types of linked servers: to SQL, Access, Excel, Oracle, text file.

Yelena

Regards,Yelena Varsha

Jan-155192 SSCrazy Points: 2613 More actions · Answer 4

Yelena,

That was my original question: how to use the page size within a linked server. But indeed, there is no option but to partition. I partitioned on the first charachter and ended up with the following code.

Thanks to all for the usefull information.

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

David Burrows SSC Guru Points: 65143 More actions · Answer 5

Can someone tell me how to create a linked server to Active Directory?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_12_94fn.asp

Far away is close at hand in the images of elsewhere.
Anon.