Retrieve >901 rows from SQL Server 2008 linked server to Active Directory

Question

In SQL Server 2008 (version 10.0.4000) I have created a linked server to an Active Directory server.

This query:

select  TOP 901 *
from  openquery(adsisca, '
select  givenName,
                sn,
                sAMAccountName          
from    ''LDAP://10.1.2.3:389''
where   objectCategory = ''Person''
        and
        objectClass = ''InetOrgPerson''
')

works.

However changing the query and trying to retrieve 902 rows does not :

select  TOP 902 *
    from  openquery(adsisca, '
    select  givenName,
                    sn,
                    sAMAccountName          
    from    ''LDAP://10.1.2.3:389''
    where   objectCategory = ''Person''
            and
            objectClass = ''InetOrgPerson''
    ')

The error is:

Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "adsisca".

I've found other instances of people discussing the same problem on forums and they never fixed it, just worked around it writing multiple views and union'ing them together for example.

Is there a more elegant fix, is there a setting I can change somewhere to retrieve more than 901 rows?

Answer 1

Use union to circumvent the limitation ..

like this :

select  TOP 901 *
from  openquery(adsisca, '
select  givenName,
                sn,
                sAMAccountName          
from    ''LDAP://10.1.2.3:389''
where   objectCategory = ''Person''
        and
        objectClass = ''InetOrgPerson''
        and
        sAMAccountName < ''m''
')
union
select  TOP 901 *
from  openquery(adsisca, '
select  givenName,
                sn,
                sAMAccountName          
from    ''LDAP://10.1.2.3:389''
where   objectCategory = ''Person''
        and
        objectClass = ''InetOrgPerson''
        and
        sAMAccountName >= ''m''
')

Answer 2

I know this is an old post, but I too had the same issues, and examined the proposed solution above. (Basically using a bunch of smaller selects with a changing criteria to keep the row count down) I just cut a slightly different version of it, and unioned them all into a Db View. I couldn't be bothered with that MaxPageSize thing - it looks too much effort.

IF NOT EXISTS(SELECT 1 FROM sys.servers WHERE name = 'ADSI') 
    EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource'

-- Create a database view from unions of smaller selects. The max 901 records thing in AD forces us to do this.
DECLARE @queryFormat VARCHAR(MAX) = '
SELECT * FROM OPENQUERY(ADSI, ''
    SELECT userPrincipalName, samAccountName, telephoneNumber, mail, middleName, givenName, sn, displayName, distinguishedName
    FROM ''''LDAP://OU=Users, OU=ABC, DC=XYZ, DC=local''''
    WHERE objectClass = ''''User'''' AND objectCategory = ''''Person'''' AND samAccountName = ''''#p0'''''')';

DECLARE @sql VARCHAR(MAX) = 'CREATE VIEW [AdView] AS ';
DECLARE @asciiValue INT = ASCII('A');
DECLARE @asciiEnd INT = ASCII('Z');
WHILE @asciiValue <= @asciiEnd BEGIN 
    SET @sql = @sql + replace(@queryFormat, '#p0', CHAR(@asciiValue) + '*');
    IF @asciiValue < @asciiEnd  SET @sql = @sql + ' UNION ALL ';
    SET @asciiValue = @asciiValue + 1;
END
--PRINT @sql;

-- the 'live' view of the active directory data.
IF OBJECT_ID('[AdView]') IS NOT NULL DROP VIEW [AdView]
EXEC(@sql);

-- a 'snapshot' of the active directory data, for faster selects. you could execute this on a schedule to keep up to date.
IF OBJECT_ID('[AdTable]', 'U') IS NOT NULL DROP TABLE [AdTable]
SELECT * INTO [AdTable] FROM [AdView]

Answer 3

You need to change the MaxPageSize setting in Active Directory. To do that you need to use Ntdsutil.exe which you can type on the run command then follow these steps

1. At the Ntdsutil.exe command prompt, type LDAP policies, and then press ENTER.
2. At the LDAP policy command prompt, type Set MaxPageSize to 2000. -> Or any number you want
3. To view the changes, type Show Values
4. To save the changes, typeCommit Changes
5. To quit, type q

Answer 4

The problem

The error is:

Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "adsisca".

I've found other instances of people discussing the same problem on forums and they never fixed it, >just worked around it writing multiple views and union'ing them together for example.

Is there a more elegant fix, is there a setting I can change somewhere to retrieve more than 901 >rows?

The solution

I just solved the same problem faced by me, without going for any Active Directory setting changes (and I am successfully able to retrieve around 50k logins from the AD and it is not missing to fetch a single login account from the AD domains):

You need to work around the ADSI query limitation by looping through the characters of the attributes. See a solution here: http://www.sqlservercentral.com/Forums/Topic231658-54-1.aspx#bm1249991

The error was resolved by writing SELECT TOP 901 ... IN PLACE OF JUST SELECT.

This problem occurred to me after migration of the database from 2005 to 2008, because in SQL Server 2008, there is a limit of 901 rows which was 1000 in SQL Server 2005 (the difference is we need to write select TOP 901, which was not required in the SQL Server 2005, else the program fails with error)