在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".

我发现其他情况下的人在论坛上讨论同样的问题，他们从来没有固定它，只是工作围绕它写多个视图和union'ing在一起的例子。

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?

推荐答案

我知道这是一个古老的职位，但我也有同样的问题，并检查上面所提出的解决方案。 （基本上用一堆小选择与不断变化的标准，以保持排倒数），我只是削减一个稍微不同的版本的它，被联合他们都到一个分贝视图。我不能打扰与maxpagesize可东西 - 它看起来太多精力

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]