Retrieving Active Directory data via a Linked Server

• James_Cronshaw

  SSC Rookie

  Points: 48

  More actions

  October 19, 2005 at 5:15 am

  #90405

  Help!!!!

  I am trying to obtain a listing of all users within our corporate active directory (approx 3,500).  I am managing to retrieve user data by using using a linked server pointing at the AD but this is where things aren't quite right - i.e. the linked server only returns 1,000 rows at a time.  When I pull down indivdual users (by altering the ad string), I manage to get all the users.

  This is the T-SQL I am using to try to retrieve all users at once:

  SELECT

  ISNULL(SAMAccountName, '') SAMAccountName,

  ISNULL(OU, '') OU,

  ISNULL(employeenumber, '') employeenumber,

  ISNULL(givenName, '') givenName,

  ISNULL(SN, '') SN,

  ISNULL(telephoneNumber, '') telephoneNumber,

  ISNULL(title, '') title,

  ISNULL(Department, '') Department,

  ISNULL(UID, '') UID,

  ISNULL(CN, '') CN

  FROM OPENQUERY(AD_TEST,'SELECT SAMAccountName, OU, employeenumber, givenName, SN, telephoneNumber, title, Department, UID, cn  FROM ''LDAP://ou=My organisation,  dc=blah, dc=blah, dc=blah'' WHERE objectCategory=''user'' ')

  The linked server itself seems to have no visible tweaking options, as I am assuming it is down to some setting within the linked server that is causing this problem!

  Can any of you lovely gurus help??

  Many thanks

  Confused AD NooB

• David Burrows

  SSC Guru

  Points: 65143

  More actions

  October 20, 2005 at 4:14 am

  #598605

  The limit of 1000 is fixed in the ADSDSOObject it cannot be altered

  However, what you could do is do several queries which return < 1000 rows at a time, eg

  '<;(&(objectCategory=user)(givenName=a*));cn;subtree'">LDAP://server/DC=xxxxxxx,DC=xx,DC=xx>;(&(objectCategory=user)(givenName=a*));cn;subtree'

  will return upto 1000 rows for givenName that start with a

  then you could repeat the query for b,c,d etc

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