sql - 错误: Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI"

Question

当我尝试查询 AD 中的用户时，我收到以下错误:

无法从链接服务器“ADSI”的 OLE DB 提供程序“ADsDSOObject”获取行。

我认为该问题是由于 1000 行限制(或 SqlServer 2008 中的 901 行)造成的。我可以对查询进行分页，但我正在寻找允许我一次检索超过 1000 个的解决方法。

如果有帮助的话，我正在使用 SqlServer 2008 R2。这是我的查询

SELECT  samaccountname AS Account, ISNULL(givenName, '''') AS givenName, ISNULL(SN, '''') AS SN, ISNULL(DisplayName, '''') as DisplayName, ISNULL(Title, '''') AS Title 
                        FROM OpenQuery(ADSI, 
                        'SELECT SamAccountName, givenName, SN, DisplayName, Title
                        FROM ''LDAP://corpdomain.corp'' 
                        WHERE objectClass = ''User'' and (SN = ''*'' or givenName = ''*'')')

有什么想法吗？

编辑-经过进一步检查，我意识到我也无法正确分页此查询。有没有人有任何关于允许我分页结果的解决方案的提示或允许我返回超过 901 的解决方法？

Answer 1

我也遇到了这个问题，并且不喜欢通常通过帐户名的第一个字母进行分页的解决方案。这意味着对 AD 进行 26 个单独的调用，并且仍然可能会失败，因为对于一个大域，很可能有超过 901 个以相同首字母开头的帐户 - 特别是如果您正在查看计算机帐户，这些帐户可能遵循一些系统使用相同的第一个字母的命名约定...

我做了一些尝试，发现如果您通过 uSNCreated 订购 openquery 并在外部查询上放置 TOP 901 子句，它不会崩溃。

因此，这是我的 SQL，它将所有事件目录对象(计算机、域 Controller 、用户和联系人)提取到包含 901 条记录的临时表中，并为您提供有关每个对象的一些有用信息。

CREATE TABLE #ADData(
    Login           NVARCHAR(256)
    ,CommonName     NVARCHAR(256)
    ,GivenName      NVARCHAR(256)
    ,FamilyName     NVARCHAR(256)   
    ,DisplayName    NVARCHAR(256)
    ,Title          NVARCHAR(256)
    ,Department     NVARCHAR(256)
    ,Location       NVARCHAR(256)
    ,Info           NVARCHAR(256)
    ,LastLogin      BIGINT
    ,flags          INT
    ,Email          NVARCHAR(256)
    ,Phone          NVARCHAR(256)   
    ,Mobile         NVARCHAR(256)
    ,Quickdial      NVARCHAR(256)
    , usnCreated    INT
)

DECLARE @Query      VARCHAR (2000)
DECLARE @Filter     VARCHAR(200)
DECLARE @Rowcount   INT

select @Filter =''

WHILE ISNULL(@rowcount,901)  = 901 BEGIN

    SELECT @Query = '
    SELECT top 901
            Login           = SamAccountName
            , CommonName    = cn
            , GivenName
            , FamilyName    = sn    
            , DisplayName
            , Title
            , Department
            , Location      = physicalDeliveryOfficeName
            , Info
            , LastLogin     = CAST(LastLogon AS bigint)
            , flags         = CAST (UserAccountControl as int)
            , Email         = mail
            , Phone         = telephoneNumber
            , Mobile        = mobile
            , QuickDial     = Pager
            , usnCreated
        FROM OPENROWSET(''ADSDSOObject'', '''', ''
                SELECT cn, givenName, sn, userAccountControl, lastLogon, displayName, samaccountname, 
                title,  department, physicalDeliveryOfficeName, info, mail, telephoneNumber, mobile, pager, usncreated
            FROM ''''LDAP://[ldap-query-string]'''' 
            WHERE objectClass=''''Person''''
            AND objectClass = ''''User''''
            ' + @filter + '
            ORDER BY usnCreated'')'             
    INSERT INTO #ADData EXEC (@Query) 
    SELECT @Rowcount = @@ROWCOUNT
    SELECT @Filter = 'and usnCreated > '+ LTRIM(STR((SELECT MAX(usnCreated) FROM #ADData)))

END

SELECT LOGIN            
        , CommonName    
        , GivenName
        , FamilyName
        , DisplayName
        , Title         
        , Department
        , Location      
        , Email         
        , Phone         
        , QuickDial     
        , Mobile        
        , Info          
        , Disabled      = CASE WHEN CAST (flags AS INT) & 2 > 0 THEN 'Y' ELSE NULL END 
        , Locked        = CASE WHEN CAST (flags AS INT) & 16  > 0 THEN 'Y' ELSE NULL END 
        , NoPwdExpiry   = CASE WHEN CAST (flags AS INT) & 65536  > 0 THEN 'Y' ELSE NULL END 
        , LastLogin     = CASE WHEN ISNULL(CAST (LastLogin AS BIGINT),0) = 0 THEN NULL ELSE 
                            DATEADD(ms, (CAST (LastLogin AS BIGINT) / CAST(10000 AS BIGINT)) % 86400000,
                            DATEADD(day, CAST (LastLogin AS BIGINT) / CAST(864000000000 AS BIGINT) - 109207, 0)) END 
        , Type = CASE WHEN flags  & 512 = 512 THEN 'user' 
                    WHEN flags IS NULL THEN 'contact' 
                    WHEN flags & 4096 = 4096 THEN 'computer'
                    WHEN flags & 532480 = 532480 THEN 'computer (DC)' END
FROM #ADData
ORDER BY Login

DROP TABLE #ADData