SQL ADSI Query limitation

  • Jan-155192

    SSCrazy

    Points: 2613

    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

  • vinny_1973

    SSCommitted

    Points: 1732

    Have a search on the site, as someone else asked the same question a week or so ago. Basically though, my understanding from the answers to that article was that the limit of 1000 records is set (hardcoded!) in AD and there is nothing you can do to change it ... therefore, paging your results was the only way to accomplish what you want!

     

  • Don Staten

    SSC Eights!

    Points: 817

    Vinny is right. ADSI is limited to returning the first 1000 records without paging. Here's a Microsoft article for reference:

    http://msdn.microsoft.com/library/en-us/adsi/adsi/paging_with_idirectorysearch.asp?frame=true

    Quote from article:

    "Searches of the Active Directory performed without paging are limited to returning a maximum of the first 1000 records, so you must use a paged search if there is the possibility that the result set will contain more than 1000 items."

  • Jan-155192

    SSCrazy

    Points: 2613

    Vinny & Newbie,

    Thanks, but I knew this. My question is: how can we do this paging from the OPENQUERY function?

    In a vb script (or for that matter any language that uses ADO), we can write something like that:

     objCommand.CommandText = _

     "<;(objectCategory=user">LDAP://dc=Central,dc=mydomain,dc=int>;(objectCategory=user)" & _

     ";distinguishedName,Name,sAMAccountName,primaryGroupID,memberOf,canonicalName,Mail,telephoneNumber;subtree"

    objCommand.Properties("Page Size") = 50

    This tells AD to send batches with 50 records at one time. But where can I put this parameter in the SQL syntax? The following doesn't work:

    SELECT employeeId, SAMAccountName, Mail

     FROM OPENQUERY( ADSI, 'SELECT Mail, SAMAccountName, employeeID FROM 'LDAP://dc=central,dc=mydomain,dc=int''WHERE objectCategory = ''Person'';''Page Size''=50' ) 

    I can partition (not page!) the query like this:

    ... FROM OPENQUERY( ADSI, 'SELECT Mail, SAMAccountName, employeeID FROM ''LDAP://dc=central,dc=mydomain,dc=int''WHERE objectCategory = ''Person'' AND SAMAccountName = ''A*''' )

    and ask for A,B,C, ... , Z. But I prefer the paging attribute, and would like to know how to submit it.

    Many thanks,

    Jan

  • Jan-155192

    SSCrazy

    Points: 2613

    By the way, I ended up with the following code. Thanks for helping.

    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

  • Don Staten

    SSC Eights!

    Points: 817

    Jan - I misunderstood your original question. Kudos on the work-around and thank you for posting it on the forum.

     I did, however,find a Microsoft article (Article 299410) that specifically addresses the paging issue within a distributed query. You must use other means to retrieve more than 1000 objects from ADSI. Quote:

    "While paging is available through ADSI's OLEDB provider, there is currently no way available to perform it from a SQL distributed query. This means that the total number of objects that can be returned for a query is the server limit. In the Windows 2000 Active Directory, the default server limit is 1,000 objects."

    http://support.microsoft.com/default.aspx?scid=kb;en-us;299410

    Don

  • jonathan.stokes

    SSC Veteran

    Points: 214

    SSC-Enthusiastic

    You are the best. I have been trying to come up with something to overcome the 1000 limit on and off for the last few weeks.

    Brilliant solution and thanks to you.

  • YSLGuru

    SSC-Insane

    Points: 21304

    Jan-155192 (10/25/2005)


    By the way, I ended up with the following code. Thanks for helping.

    Jan

    CREATE TABLE #tmpADUsers

    ( employeeIdvarchar(10) NULL,

    SAMAccountNamevarchar(255) NOT NULL,

    emailvarchar(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

    I know this reply is over a year later but I came across your post while searching on the ADSI Linked server provider. I can get your sample to work but I hit a brick wall when I try to get too many columns of data and I can't see where you change this if at all. I'm trying to access 5 more items in addition to the 3 in your code and I can access all of them if I select only part of the 8 fields at once. For example of the 8 I'm trying to get:

    employeeID, SAMAccountName, Mail, DisplayName, sn, givenName, department & userAccountControl

    I can get any 5-6 but if I try for 6 to 8, depending on the combination I get an error.

    Thoughts?

    Kindest Regards,

    Just say No to Facebook!
  • Brian ODwyer

    SSC Enthusiast

    Points: 198

    I found that to be true as well, think it is something to do with the string length, used * to just pull all the columns and save on the string length instead of directly naming then in the select. Tried increasing the string length from 255 to 512 and it did not work. Will research it.

  • Brian ODwyer

    SSC Enthusiast

    Points: 198

    Definite 255 char limit. See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65584

    I used the function to get around and loop through

    declare @Apptemp3 table (BSamName varchar(255),

    BDisplayName varchar(255),

    BPhone varchar(100),

    BEMail varchar(255),

    BFirstName varchar(100),

    BLastName varchar(100))

    DECLARE @sChar char(1)

    declare @body varchar(8000)

    DECLARE @nAsciiValue smallint

    SELECT @nAsciiValue = 65

    WHILE @nAsciiValue < 91

    BEGIN

    SELECT @sChar= CHAR(@nAsciiValue)

    SET @body = (select dbo.fnSprintf('SELECT sAMAccountName, displayName, mail, telephoneNumber, sn, givenName FROM OPENQUERY( ADSI, ''SELECT displayName, sAMAccountName, mail, telephoneNumber, sn, givenName FROM ''''LDAP://dc=cchcs,dc=ldap''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )', @SChar +'*', default))

    INSERT @Apptemp3

    EXEC( @body)

    SELECT @nAsciiValue = @nAsciiValue + 1

    END

    Select ISNULL(Upper(BSamName),'NoSamID'),ISNULL(BDisplayName,'NoDName'),ISNULL(BPhone,'NoPhone'),ISNULL(BEMail,'Nomail'),ISNULL(BFirstName,'NoFname'),ISNULL(BLastName,'NoLname') from @Apptemp3

    order by BSamName

  • Tracy Nolan

    SSC Enthusiast

    Points: 171

    Excellent, works like a charm!!

  • christoph.hackstein 38861

    SSC Journeyman

    Points: 83

    What about http://www.selfadsi.de/search.htm#MaxPageSizeADS

  • kerry.smith 57835

    SSC Rookie

    Points: 29

    Brian ODwyer - Thursday, February 9, 2012 2:54 PM

    Definite 255 char limit. See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65584I used the function to get around and loop throughdeclare @Apptemp3 table (BSamName varchar(255), BDisplayName varchar(255), BPhone varchar(100), BEMail varchar(255), BFirstName varchar(100), BLastName varchar(100))DECLARE @sChar char(1)declare @body varchar(8000)DECLARE @nAsciiValue smallintSELECT @nAsciiValue = 65WHILE @nAsciiValue < 91 BEGIN SELECT @sChar= CHAR(@nAsciiValue)SET @body = (select dbo.fnSprintf('SELECT sAMAccountName, displayName, mail, telephoneNumber, sn, givenName FROM OPENQUERY( ADSI, ''SELECT displayName, sAMAccountName, mail, telephoneNumber, sn, givenName FROM ''''LDAP://dc=cchcs,dc=ldap''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )', @SChar +'*', default)) INSERT @Apptemp3 EXEC( @body) SELECT @nAsciiValue = @nAsciiValue + 1 ENDSelect ISNULL(Upper(BSamName),'NoSamID'),ISNULL(BDisplayName,'NoDName'),ISNULL(BPhone,'NoPhone'),ISNULL(BEMail,'Nomail'),ISNULL(BFirstName,'NoFname'),ISNULL(BLastName,'NoLname') from @Apptemp3order by BSamName

    The above example was great but I ran into an issue where there were more than 1000 's' names and even more than 1000 service accounts with 's.*' naming format.  

    While not elegant my script below loops through the different parts of the alphabet and handles s.  Hope this helps somebody else.

    --Hitting row limitations at 's' > chokes on services

    DROP TABLE #tmpADUsers;

    CREATE TABLE #tmpADUsers
    (
      employeeId VARCHAR(10) NULL,
      SAMAccountName VARCHAR(255) NOT NULL,
      givenname VARCHAR(255) NULL,
      sn VARCHAR(255) NULL,
      email VARCHAR(255) NULL,
      CN VARCHAR(255) NULL
    );

    GO
    -- loop through the alphabet from A-R

    DECLARE @cmdstr VARCHAR(255);
    DECLARE @nAsciiValue SMALLINT;
    DECLARE @sChar CHAR(1);

    SELECT @nAsciiValue = 65;

    WHILE @nAsciiValue < 83
    BEGIN

      SELECT @sChar = CHAR(@nAsciiValue);

      EXEC master..xp_sprintf @cmdstr OUTPUT,
              'SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
    FROM OPENQUERY( ADSI, ''SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
    FROM ''''LDAP://DC=corp,DC=symetra,DC=com''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )',
              @sChar;

      INSERT #tmpADUsers
      EXEC (@cmdstr);

      SELECT @nAsciiValue = @nAsciiValue + 1;
    END;

    -- loop through the alphabet from T-Z
    GO
    DECLARE @cmdstr VARCHAR(255);
    DECLARE @nAsciiValue SMALLINT;
    DECLARE @sChar CHAR(1);

    SELECT @nAsciiValue = 84;

    WHILE @nAsciiValue < 91
    BEGIN

      SELECT @sChar = CHAR(@nAsciiValue);

      EXEC master..xp_sprintf @cmdstr OUTPUT,
              'SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
    FROM OPENQUERY( ADSI, ''SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
    FROM ''''LDAP://DC=corp,DC=symetra,DC=com''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )',
              @sChar;

      INSERT #tmpADUsers
      EXEC (@cmdstr);

      SELECT @nAsciiValue = @nAsciiValue + 1;
    END;

    -- pull in s followed by an alpha
    GO
    DECLARE @cmdstr VARCHAR(255);
    DECLARE @nAsciiValue SMALLINT;
    DECLARE @sChar CHAR(1);

    SELECT @nAsciiValue = 84;

    WHILE @nAsciiValue < 91
    BEGIN

      SELECT @sChar = CHAR(@nAsciiValue);

      EXEC master..xp_sprintf @cmdstr OUTPUT,
              'SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
    FROM OPENQUERY( ADSI, ''SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
    FROM ''''LDAP://DC=corp,DC=symetra,DC=com''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''s%s*'''''' )',
              @sChar;

      INSERT #tmpADUsers
      EXEC (@cmdstr);

      SELECT @nAsciiValue = @nAsciiValue + 1;
    END;

    GO
    --this pulls in s with periods as the 2nd character
    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, givenname, sn, Mail, CN
    FROM OPENQUERY( ADSI, ''SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
    FROM ''''LDAP://DC=corp,DC=symetra,DC=com'''' WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''s.%s*'''''' )',
              @sChar;

      INSERT #tmpADUsers
      EXEC (@cmdstr);

      SELECT @nAsciiValue = @nAsciiValue + 1;
    END;

    SELECT *
    FROM #tmpADUsers
    ORDER BY SAMAccountName;

Viewing 13 posts - 1 through 13 (of 13 total)

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