Overcoming the OPENQUERY Record Limit

• Ivanov Evgeny

  Grasshopper

  Points: 11

  More actions

  November 14, 2014 at 8:20 am

  #1758617

  I faced with AD limitations on my work and the best solution i choosed - use MSADHelper.dll by Igor Kovalenko.

  http://blogs.msdn.com/b/ikovalenko/archive/2007/03/22/how-to-avoid-1000-rows-limitation-when-querying-active-directory-ad-from-sql-2005-with-using-custom-code.aspx

  It works perfect.

• frost_tom

  SSC Veteran

  Points: 221

  More actions

  November 15, 2014 at 5:00 am

  #1758784

  I would suggest that this article be removed. There are so many things wrong with it ...

• hjackson67 52048

  Old Hand

  Points: 335

  More actions

  November 18, 2014 at 11:02 am

  #1759360

  The suggestion to remove the article because the author incorrectly identifies the 1000 row limit as being imposed by OPENQUERY and not LDAP is no reason to remove the article. We learn much more from mistakes than virtually any other source. I'm sure that this lesson will stick with many readers, who were not previously familiar with the issue, precisely because of the error in the articles' content.

  By the way, I got around the LDAP limitation by creating a view that simply UNION-ed the queries together based on first 2 numbers of the EmployeeID. Here's a snippet:

  Create VIEW [ADInfo].[vw_AD_Extract]

  AS

  SELECT

  [DisplayName], [DistinguishedName], Department, [First Name], [Last Name], sAMAccountName, TelePhoneNumber, mail, Title,EmployeeNumber, ObjectGUID

  FROM

  (

  Select [DisplayName], [DistinguishedName], Department, givenName [First Name], sn[Last Name], sAMAccountName, TelePhoneNumber, mail, Title,EmployeeNumber, ObjectGUID

  FROM OpenQuery(ADSI,

  '<LDAP://COMPANYNAME.com>;(&(objectCategory=Person)(objectClass=user)(EmployeeID=00*));DisplayName, DistinguishedName, Department, GivenName, SN, sAMAccountName, TelePhoneNumber, mail, Title, EmployeeNumber,ObjectGUID' )

  AS derivedtbl_10

  UNION

  Select [DisplayName], [DistinguishedName], Department, givenName [First Name], SN [Last Name], sAMAccountName, TelePhoneNumber, mail, Title,EmployeeNumber, ObjectGUID

  FROM OpenQuery(ADSI,

  '<LDAP://COMPANYNAME.com>;(&(objectCategory=Person)(objectClass=user)(EmployeeID=01*));DisplayName, DistinguishedName, Department, GivenName, SN, sAMAccountName, TelePhoneNumber, mail, Title, EmployeeNumber,ObjectGUID' )

  AS derivedtbl_9