SQL LDAP Query not returning all users

I am attempting to query my LDAP structure using the following SQL LDAP query and I am able to pull most all the users by samaccountname but I have a few users that the query is not finding.  I have verified using a LDAP Browser  to verify that the users do exist and the samaccountname is consist with all the other accounts.  Any ideas why this might be happening?
SELECT 
      sAMAccountName
      , ISNULL(mail,'') as mail
      , employeeId 
FROM OPENQUERY(ADSI, 'SELECT sAMAccountName, mail, employeeId FROM ''LDAP://OURSERVER/CN=Users,DC=ONE,DC=TWO,DC=THREE,DC=com''')
order by sAMAccountName

Open in new window

ittnvAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you get some 1000 records, and there are more, right?
well, LDAP is indeed limited to return 1000 records at once, so you have to get the data in chunks...
0
ittnvAuthor Commented:
Hmmm...That's interesting...but you let's say you have 1030 users and you run the query.  Would you only get a subset of the orginal 1030 or would you get the first 1000 entries in LDAP and then have to modify the query to get the next 30?  It appears that we are skipping users in our LDAP query rather than getting the first 1000?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
depending on what you specify as "first" :)

what I used is a loop to get all users A*, then B*, then C* etc ...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

St3veMaxCommented:
There is an easier way. I'll pop a ZIP up in a moment.
0
St3veMaxCommented:
File 1 is a Stored Proc which should go in a DB of your choice but not a system DB.
File 2 is the code to export the contents of AD into a table of your choice. You can populate this into a table. I've commented the insert bit out as I didnt have time to sort out an issue with a field length, but it's enough to give you the idea.

HTH

p.s. this query pulled over 4K rows from our AD.
1---Sync.usp-SetupOLE.txt
2---Export-from-AD.txt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ittnvAuthor Commented:
Thanks St3veMax for the code we'll take a look at it and see how it may help.  However we have discovered additional information related to the openquery ADSI link.  Since the link is used to access AD, permissions are in place to authenticate the user performing the query.  It appears as if our developer may be running into a permissions issue, so this leads to two questions.
1) does this sound accurate in reguards to AD and ADSI
2) where would I find how to modify such permissions
 
0
St3veMaxCommented:
I'm not sure if you will still be subject to the 1000 record limit...

Using the attached is very straight forward and doesent need any linked server.

HTH
0
ittnvAuthor Commented:
OK thanks to the code that St3veMax sent we found the problem.  It was not an issue with permissions it was an incorrectly structured query.  We have fixed the query and all is well.  Thanks to all for the help but St3ve get's the points for this one!
Corrected code is below:
select      
      Name
      ,Title
      ,TelephoneNumber
      ,sAMAccountName
      ,mail
      ,employeeId 
from  openquery(ADSI,
      'select     Name, Title, TelephoneNumber, sAMAccountName, mail, employeeId
       FROM ''LDAP://OURDCSERVER/CN=Users,DC=ONE,DC=TWO,DC=THREE,DC=com''
       where      objectClass = ''User''')
order by Name

Open in new window

0
ittnvAuthor Commented:
Nice code, we will use this for other queries as well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.