SDeezy SDeezy - 4 months ago 287
SQL Question

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

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

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)