从 SQL Server 2008 链接服务器检索 >901 行到 Active Directory [英] Retrieve >901 rows from SQL Server 2008 linked server to Active Directory

问题描述

在 SQL Server 2008(版本 10.0.4000)中，我创建了一个链接到 Active Directory 服务器的服务器.

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

这个查询:

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

有效.

但是更改查询并尝试检索 902 行不会:

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

错误是:

消息 7330，级别 16，状态 2，第 1 行无法从 OLE DB 中获取一行链接的提供程序ADSDSOObject"服务器adsisca".

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.

是否有更优雅的修复方法，是否可以在某处更改设置以检索超过 901 行?

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

推荐答案

使用联合来规避限制..

Use union to circumvent the limitation ..

像这样:

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

这篇关于从 SQL Server 2008 链接服务器检索 >901 行到 Active Directory的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！