当前位置：theitroad>SQL 错误：无法从链接服务器“ADSI”的 OLE DB 提供程序“ADsDSOObject”中获取一行

SQL 错误：无法从链接服务器“ADSI”的 OLE DB 提供程序“ADsDSOObject”中获取一行

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/8594775/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 13:43:16 　来源:igfitidea点击:

Error : Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI"

sqlsql-serversql-server-2008

提问by WhizBang

When I attempt to query AD for users I receive the following error:

当我尝试为用户查询 AD 时，我收到以下错误：

Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".

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

I assume the issue due to the 1000 row limit (or 901 rows in SqlServer 2008). I can page the query but I am looking for workaround that would allow me to retrieve more than 1000 at a time.

我假设问题是由于 1000 行限制（或 SqlServer 2008 中的 901 行）。我可以分页查询，但我正在寻找可以让我一次检索 1000 多个的解决方法。

In case it helps, I am using SqlServer 2008 R2. and here is my query

如果有帮助，我正在使用 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 = ''*'')')

Any ideas?

有任何想法吗？

EDIT - Upon further inspection I realized I am not able to properly page this query either. Does anyone have any tips on a solution that will allow me to page the results or a workaround that will allow me to return more than 901?

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

采纳答案by Mazhar Ehsan

The problem

问题

When I attempt to query AD for users I receive the following error:

Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".

I assume the issue due to the 1000 row limit (or 901 rows in SqlServer 2008). I can page the query >but I am looking for workaround that would allow me to retrieve more than 1000 at a time.

In case it helps, I am using SqlServer 2008 R2. and here is my query >

当我尝试为用户查询 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 = ''*''

Any ideas?

EDIT - Upon further inspection I realized I am not able to properly page this query either. Does >anyone have any tips on a solution that will allow me to page the results or a workaround that will >allow me to return more than 901?

有任何想法吗？

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

My workaround

我的解决方法

I just solved the same problem faced by me, by applying paging optimally (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):

我刚刚解决了我面临的同样问题，通过优化应用分页（并且我成功地能够从 AD 检索大约 50k 登录，并且不会丢失从 AD 域获取单个登录帐户）：

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

您需要通过遍历属性的字符来解决 ADSI 查询限制。在此处查看解决方案：http: //www.sqlservercentral.com/Forums/Topic231658-54-1.aspx#bm1249991

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

通过编写SELECT TOP 901 ...IN PLACE OF JUST解决了错误SELECT。

And yes, this problem is related to using SqlServer 2008 R2. 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)

是的，这个问题与使用 SqlServer 2008 R2 有关。这个问题是我从 2005 年迁移到 2008 年数据库后出现的，因为在 SQL Server 2008 中，有 901 行的限制，而在 SQL Server 2005 中是 1000（不同的是我们需要写 select TOP 901，这不是SQL Server 2005 中需要，否则程序会失败并显示错误）

回答by John Sinclair

I hit this problem too, and didn't like the usual solution posted of paging by the first letter of the account name. This would mean 26 separate calls to AD, and also could still potentially fail because with a large domain its very possible to have more than 901 accounts starting with the same first letter - particularly if you are looking at computer accounts, which likely follow some systematic naming convention using the same first letter...

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

I did some playing around and I found that if you order the openquery by uSNCreated and put a TOP 901 clause on the outer query it doesn't blow up.

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

So, here is my SQL which fetches ALL active directory objects (computers, domain controllers, users and contacts) into a temp table in chunks of 901 records and gives you some useful information on each object.

所以，这是我的 SQL，它将所有活动目录对象（计算机、域控制器、用户和联系人）以 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

回答by JamieSee

From your reply to my comment it sound like an SSIS package fired by a SQL Agent job would be an ideal way to go. Here's how you can access Active Directory in SSIS:

从您对我的评论的回复来看，听起来由 SQL 代理作业触发的 SSIS 包将是一种理想的方式。以下是在 SSIS 中访问 Active Directory 的方法：

  1. Create a new SSIS Project
  2. Add a Data Flow Task to the Control Flow.
  3. Click the Data Flow tab.
  4. Drag an ADO NET Source from the Toolbox to the Data Flow.
  5. Double-click the ADO NET Source.
  6. Click the New button next to OLE DB connection manager.
  7. Click the New button in the Configure ADO.NET Connection Manager dialog.
  8. Click the downward pointing arrow on the Provider dropdown.
  9. Find .Net Providers for OleDb in the list and double-click it.
  10. Find OLE DB Provider for Microsoft Directory Services in the list and double-click it.
  11. Click the OK button.
  12. In Server or file name put ActiveDirectory.
  13. Highlight ActiveDirectory in the Data connections.
  14. Click the OK button.
  15. Change the Data access mode to SQL command.
  16. In the SQL command text box enter <LDAP://DC=domain,DC=tld>;(&(objectClass=User)(objectCategory=Person));distinguishedName,displayName,sn,givenName,middleName,mail,telephoneNumber;subtree.
  17. Change domain and tld to the appropriate identifiers for your domain and add any other appropriate LDAP path elements.
  18. Add any other appropriate ActiveDirectory attributes to the query.
  19. Click the OK button.
  20. You will see error messages indicating that the data type "System.Object" is not supported. These can be ignored.
  21. Click the OK button on the warning dialog.
  22. Right-click on the ADO NET Source.
  23. Click Properties.
  24. Change ValidateExternalMetadata to False.
  1. 创建一个新的 SSIS 项目
  2. 将数据流任务添加到控制流。
  3. 单击数据流选项卡。
  4. 将 ADO NET 源从工具箱拖到数据流。
  5. 双击 ADO NET 源。
  6. 单击 OLE DB 连接管理器旁边的新建按钮。
  7. 单击“配置 ADO.NET 连接管理器”对话框中的“新建”按钮。
  8. 单击提供程序下拉菜单上的向下箭头。
  9. 在列表中找到 OleDb 的 .Net Providers 并双击它。
  10. 在列表中找到 OLE DB Provider for Microsoft Directory Services 并双击它。
  11. 单击确定按钮。
  12. 在服务器或文件名中放置 ActiveDirectory。
  13. 在数据连接中突出显示 ActiveDirectory。
  14. 单击确定按钮。
  15. 将数据访问模式更改为 SQL 命令。
  16. 在 SQL 命令文本框中输入 <LDAP://DC=domain,DC=tld>;(&(objectClass=User)(objectCategory=Person));distinguishedName,displayName,sn,givenName,middleName,mail,telephoneNumber;subtree.
  17. 将 domain 和 tld 更改为您的域的适当标识符，并添加任何其他适当的 LDAP 路径元素。
  18. 向查询添加任何其他适当的 ActiveDirectory 属性。
  19. 单击确定按钮。
  20. 您将看到指示不支持数据类型“System.Object”的错误消息。这些可以忽略。
  21. 单击警告对话框上的确定按钮。
  22. 右键单击 ADO NET 源。
  23. 单击属性。
  24. 将 ValidateExternalMetadata 更改为 False。

You may also want to do the steps below, but be aware that if you do this and have an Active Directory attribute longer than 4000 characters, it will be truncated in the data flow.

您可能还想执行以下步骤，但请注意，如果您执行此操作并且具有超过 4000 个字符的 Active Directory 属性，它将在数据流中被截断。

  1. Right-click on the ADO NET Source.
  2. Click Show Advanced Editor.
  3. Go to the Input and Output Properties tab.
  4. Expand ADO NET Source Output.
  5. Expand Output Columns.
  6. For each column, change the DataType to Unicode string [DT_WSTR] and set the Length to 4000.
  7. Click the OK button.
  8. Double-click the ADO NET Source go to Error Output.
  9. Select all the values for the rows under Trunctation.
  10. List item
  11. Under Set this value to selected cells choose Ignore failure.
  12. Click the Apply button.
  13. Click the OK button.
  1. 右键单击 ADO NET 源。
  2. 单击显示高级编辑器。
  3. 转到输入和输出属性选项卡。
  4. 展开 ADO NET 源输出。
  5. 展开输出列。
  6. 对于每一列，将数据类型更改为 Unicode 字符串 [DT_WSTR]，并将长度设置为 4000。
  7. 单击确定按钮。
  8. 双击 ADO NET 源转到错误输出。
  9. 为截断下的行选择所有值。
  10. 项目清单
  11. 在将此值设置为选定单元格下，选择忽略失败。
  12. 单击应用按钮。
  13. 单击确定按钮。

Note that this query format is also supported:

请注意，还支持此查询格式：

SELECT     distinguishedName, displayName, sn, givenName, middleName, mail, telephoneNumber
FROM         'LDAP://DC=domain,DC=tld'
WHERE     objectClass = 'User' AND objectCategory = 'Person'

See the MSDN article Microsoft OLE DB Provider for Microsoft Active Directory Servicefor more information on the query formats supported by the provider.

有关提供程序支持的查询格式的更多信息，请参阅 MSDN 文章Microsoft OLE DB Provider for Microsoft Active Directory Service。

回答by Nestalna

I solved it using another post by Magnus Reuter -just thought I gave y'all a link because it is simple ans ingenious!

我使用 Magnus Reuter 的另一篇文章解决了这个问题——我以为我给了你们一个链接，因为它既简单又巧妙！

He did 2 queries, joining them by using UNION, but for the first one he selected all sAMAccountname m. Of course if you find that your middle is not the letter "m" you can adjust that accordingly, but generally if you have around 1000-2000 records it will be "m".

他做了 2 个查询，使用 UNION 加入它们，但对于第一个，他选择了所有 sAMAccountname m。当然，如果你发现你的中间不是字母“m”，你可以相应地调整它，但通常如果你有大约 1000-2000 条记录，它会是“m”。

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

从 SQL Server 2008 链接服务器检索 >901 行到 Active Directory

回答by Jason Geiger

There is an additional reason why you might get this error. If you are using multiple domains you may need to change the Active Directory properties for this SQL Server. Selecting "Trust this computer for delegation to any service (Kerberos only)" should correct the problem if the cause is from "double hop" in a multi-domain setup.

您可能会收到此错误还有另一个原因。如果您使用多个域，您可能需要更改此 SQL Server 的 Active Directory 属性。如果原因来自多域设置中的“双跳”，则选择“信任此计算机以委托给任何服务（仅限 Kerberos）”应该可以解决问题。

Active Directory Server Properties

Active Directory 服务器属性

回答by Brenton

This exact same Cannot fetch a row from OLE DB provider "ADSDSOObject"error message can also occur when you have less than 1000 rows in the resultset, but something else prevents SQL from fetching the records. We recently had a situation where the SQL Service account password was out of date, and this seems to have caused this error. Updating the password in the Log on details of the service and restarting SQL Server fixed it.

当结果集中的行少于 1000 行时，也可能出现完全相同的无法从 OLE DB 提供程序“ADSDSOObject”中获取行的错误消息，但其他原因会阻止 SQL 获取记录。我们最近遇到了 SQL 服务帐户密码过期的情况，这似乎是导致此错误的原因。更新服务的登录详细信息中的密码并重新启动 SQL Server 修复了它。

Just thought I would tack this answer on here so if someone googles this error message in future this might also help!

只是想我会在这里加上这个答案，所以如果将来有人用谷歌搜索这个错误信息，这也可能有帮助！

相关推荐

SQL Server 存储过程捕获 T-SQL 中的返回值

SQL Server 存储过程捕获 T-SQL 中的返回值

SQL 在 postgresql 上将默认值设置为 null

SQL 在 postgresql 上将默认值设置为 null

SQL Server 2008 Express CONCAT() 不存在？

SQL Server 2008 Express CONCAT() 不存在？

如何同时使用带有 NOT IN 或 equals 的 SQL WHERE CASE？

如何同时使用带有 NOT IN 或 equals 的 SQL WHERE CASE？

相关推荐
最近更新
标签