Returning more than 1000 records from AD via T/SQL

[mrdenny]

I'm trying to pull a list of all accounts from Active directory into a SQL Table for processing.

I've got my query working fine.

select *
 from openquery
(
ADSI,'SELECT cn, mail, sAMAccountName, userAccountControl
FROM ''LDAP://domain.com''
WHERE objectCategory = ''Person'' 
AND objectClass = ''user''
order by mail
'
)

It returns the records I'm looking for. But it hits the 1000 record mark (we have about 1700 employees).

Does anyone have a good method of getting more additional pages of records from the AD network.

I've found many ways to do this in ADO, but I don't want to use ADO, I want to do all my processing directly through T/SQL.

Does any one have any ideas?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Catadmin]

Denny,

This may be a limitation of the connection. I'll look around and see if I can't find any more info.

In the meantime, have you tried limiting the recordset with a WHERE Count subquery or a WHERE AcctName Like query and running the query twice?

I did find a link about exporting AD accounts to a CSV file using an existing Windows Utility, which I'm sure you could use DTS to import into SQL.

http://www.computerperformance.co.uk/Logon/Logon_CSVDE_Export.htm

Another link regarding export (different tool):

http://support.microsoft.com/kb/q237677/

Does any of this help?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

The SQL queries that LDAP will respond to are very few and far between. It doesn't seem to support much.
I'd like to avoid using an outside tool to export the data then import it.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Catadmin]

Denny,

The only other tool I know if to use (but it exports to a flat file, not SQL) is the 'Active Directory Administration Tool' with a funny purple icon.

This information I got from an Exchange Admin who has had to export info from AD for mail upgrades.

Sorry, can't find any other information on this particular subject. @=/






Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[mrdenny]

I was hopping that there would be an easier way. That sucks.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Warlocks]

Guys,

Very interested in this post with regards how you set up the linked server to look at AD.
I had a quick look in the types of linked server and also any ODBC connection types but couldn't see anything referencing AD etc.
I was looking on my machine (XP Pro).
Am I missing something?

Cheers,

Woody.

 

[mrdenny]

This is the code I used to create the linked server.

exec sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

The ADSDSOObject isn't an option in the drop down menu. It's one of those things you just need to do via code.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[mrdenny]

I've broken down and used CSVDE to export the AD List, then used DTS to import it into SQL.

I'm calling everything from T/SQL do I can still run everything from within my stored proc, so I'm happy about that at least.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)