Hello, I have a similar problem. I implemented the suggested solution that queries AD without using a linked server and it worked great on my instance of SQL 2008 Enterprise running on Windows 2003. However, when I tried to move it over to an instance of SQL 2008 Enterprise running on any flavor of Windows 2008 it fails.

Actually to avoid the 1000 row limit of AD (which I have upped to 3000 via ADSIEdit), I split my query into two parts and then run a "Union" query to combine the results. Each query brings back around 400 rows and then union into around 800 results.

When I try to run one of the initial queries via the SSMS and "Select Top 1000 rows" on a SQL server that is running Windows 2003 it works, but do the same on a server running any flavor of Windows 2008 or 2008 R2 (32 or 64bit) and it displays the results then displays the message:

"Msg 7330, Level 16, State 2, Line 2" "Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "(null)"."

Both of the initial queries display that same message and if you try to run the "Union" query, it returns no results.

One of the queries that I am running (the other gets the rest of the alphabet and then the union query merges the two) is:

SELECT TOP (100) PERCENT cn, employeeID, samAccountName, mail, givenName, sn

FROM OPENROWSET('ADSDSOObject', 'adsdatasource;',

'SELECT sn, givenName, mail, samAccountName, employeeID, cn

FROM ''LDAP://Insert FQDN here'' where objectCategory = ''Person'' and objectClass = ''User'' ')

AS derivedtbl_1

WHERE (employeeID IS NOT NULL) AND (samAccountName LIKE 'a%' OR

samAccountName LIKE 'b%' OR

samAccountName LIKE 'c%' OR

samAccountName LIKE 'd%' OR

samAccountName LIKE 'e%' OR

samAccountName LIKE 'f%' OR

samAccountName LIKE 'g%' OR

samAccountName LIKE 'h%' OR

samAccountName LIKE 'i%' OR

samAccountName LIKE 'j%')

ORDER BY cn

Naturally the "insert FQDN here" is really populated with our domain name in a format like "search.microsoft.com".

If I modify the "Select Top (100) Percent" to be equal to the actual number of rows that should return (or lower), then the query is successful on a Windows 2008 server. However the number of users is constantly changing and I do not want to miss anyone, so I want it to find all and return the proper values without me having to specify a number to return.

All of my SQL services are running as a domain user with rights to query AD.

Any ideas why this works on a Windows 2003 server but not a Windows 2008 server? We want to retire our Windows 2003 SQL server, but need the functionality provided by the Active Directory query.

FYI, no linked server was necessary on the Windows 2003 server.

Thanks,

Jeremy Hawks

Systems Administrator

Green River Community College