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''
')