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

Question

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

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?

Answer 1

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

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.

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.

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

Answer 2

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 >

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?

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

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

The error was resolved by writing 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)

Answer 3

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:

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.

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.

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.

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 Service for more information on the query formats supported by the provider.

Answer 4

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.

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