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

Question

In SQL Server 2008 (version 10.0.4000), I created a linked server on an Active Directory server.

This request:

select TOP 901 * from openquery(adsisca, ' select givenName, sn, sAMAccountName from ''LDAP://10.1.2.3:389'' where objectCategory = ''Person'' and objectClass = ''InetOrgPerson'' ') 

work.

However, changing the request and attempting to extract 902 rows does not occur:

 select TOP 902 * from openquery(adsisca, ' select givenName, sn, sAMAccountName from ''LDAP://10.1.2.3:389'' where objectCategory = ''Person'' and objectClass = ''InetOrgPerson'' ') 

Error:

Msg 7330, Level 16, State 2, Line 1 Unable to get the row from the OLE DB provider "ADSDSOObject" for the linked server "adsisca".

I found other cases when people discussed the same problem in the forums, and they never fixed it, just worked on it, recording several views and combining them together, for example.

Is there a more elegant fix, is there a parameter that I can change somewhere to get more than 901 lines?

Answer 1

Use union to get around the limitation.

like this:

 select TOP 901 * from openquery(adsisca, ' select givenName, sn, sAMAccountName from ''LDAP://10.1.2.3:389'' where objectCategory = ''Person'' and objectClass = ''InetOrgPerson'' and sAMAccountName < ''m'' ') union select TOP 901 * from openquery(adsisca, ' select givenName, sn, sAMAccountName from ''LDAP://10.1.2.3:389'' where objectCategory = ''Person'' and objectClass = ''InetOrgPerson'' and sAMAccountName >= ''m'' ') 

Answer 2

I know this is an old post, but I also had the same problems, and considered the proposed solution above. (Basically, using a bunch of smaller samples with varying criteria to keep a row count), I just slightly modified its version and merged them into Db View. I could not be bothered that MaxPageSize - it looks too much.

 IF NOT EXISTS(SELECT 1 FROM sys.servers WHERE name = 'ADSI') EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource' -- Create a database view from unions of smaller selects. The max 901 records thing in AD forces us to do this. DECLARE @queryFormat VARCHAR(MAX) = ' SELECT * FROM OPENQUERY(ADSI, '' SELECT userPrincipalName, samAccountName, telephoneNumber, mail, middleName, givenName, sn, displayName, distinguishedName FROM ''''LDAP://OU=Users, OU=ABC, DC=XYZ, DC=local'''' WHERE objectClass = ''''User'''' AND objectCategory = ''''Person'''' AND samAccountName = ''''#p0'''''')'; DECLARE @sql VARCHAR(MAX) = 'CREATE VIEW [AdView] AS '; DECLARE @asciiValue INT = ASCII('A'); DECLARE @asciiEnd INT = ASCII('Z'); WHILE @asciiValue <= @asciiEnd BEGIN SET @sql = @sql + replace(@queryFormat, '#p0', CHAR(@asciiValue) + '*'); IF @asciiValue < @asciiEnd SET @sql = @sql + ' UNION ALL '; SET @asciiValue = @asciiValue + 1; END --PRINT @sql; -- the 'live' view of the active directory data. IF OBJECT_ID('[AdView]') IS NOT NULL DROP VIEW [AdView] EXEC(@sql); -- a 'snapshot' of the active directory data, for faster selects. you could execute this on a schedule to keep up to date. IF OBJECT_ID('[AdTable]', 'U') IS NOT NULL DROP TABLE [AdTable] SELECT * INTO [AdTable] FROM [AdView] 

Answer 3

Problem

Error:

Msg 7330, Level 16, State 2, Line 1 Unable to get the row from the OLE DB provider "ADSDSOObject" for the linked server "adsisca".

I found other cases when people discussed the same problem on the forums, and they never fixed it,> just worked on it, creating several types and combining them together, for example.

Is there a more elegant fix, is there a parameter that I can change somewhere to get more than 901 lines?

Decision

I just solved the same problem that I encountered without changing any changes in the Active Directory settings (and I can successfully get about 50 thousand logins from AD, and it’s not enough to get one account from AD domains):

You need to get around the ADSI query restriction by going through attribute characters. See the solution here: http://www.sqlservercentral.com/Forums/Topic231658-54-1.aspx#bm1249991

The error was resolved by writing SELECT TOP 901 ... IN LOCATION ONLY SELECT .

I got this problem after migrating 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 that we need to write select TOP 901, which was not required in SQL Server 2005, otherwise the program will fail)

Answer 4

I did not like the smell of the other options posted here, which is very important, because with a large domain it is very possible to have more than 901 accounts starting with the same first letter, especially if you look at computer accounts that probably follow some systematic naming conventions using the same first letter ...

I played around a bit and I found that if you order openquery using uSNCreated and put the TOP 901 clause in an external query, it will not explode.

So, here is my SQL, which extracts ALL active directory objects (computers, domain controllers, users and contacts) into a temporary table in pieces of 901 records and gives you useful information about 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 5

You need to change the MaxPageSize parameter in Active Directory. To do this, you need to use Ntdsutil.exe, which you can enter in the run command, and then follow these steps.

• At the Ntdsutil.exe command prompt, type LDAP policies and then press ENTER.
• At the LDAP policy command line, enter Set MaxPageSize to 2000 . → Or any number you want
• Type Show Values to view the changes.
• Enter Commit Changes to save your changes.
• Enter q to exit

Answer 6

I need to change the MaxTempTableSize parameter in Active Directory. To do this, you need to use Ntdsutil.exe, which you can enter in the run command, and then follow these steps.

 At the Ntdsutil.exe command prompt, type LDAP policies, and then press ENTER. At the LDAP policy command prompt, type Set MaxTempTableSize to 2000. -> Or any number you want To view the changes, type Show Values To save the changes, typeCommit Changes To quit, type q 

Answer 7

This version of the solution addresses situations where the number of users starting with the specified character is still> 901. It uses a master procedure that calls another stored procedure.

 -- This procedure pulls a subset of LDAP users CREATE PROC [dbo].[Select_LDAP_Rows] ( @MyChar CHAR(1) ) AS BEGIN --DECLARE @MyChar CHAR(1) = 'A'; DECLARE @queryFormat VARCHAR(MAX) = ' SELECT * FROM OPENQUERY(ADSI, '' SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber FROM ''''LDAP://OU=PHC,dc=MyCompany,dc=org'''' WHERE objectClass = ''''User'''' AND objectCategory = ''''Person'''' AND displayName = ''''' + @MyChar + '#p0'''''') '; DECLARE @sql VARCHAR(MAX) = 'CREATE VIEW [AdView] AS '; DECLARE @asciiValue INT = ASCII('A'); DECLARE @asciiEnd INT = ASCII('Z'); WHILE @asciiValue <= @asciiEnd BEGIN SET @sql = @sql + replace(@queryFormat, '#p0', CHAR(@asciiValue) + '*'); IF @asciiValue < @asciiEnd SET @sql = @sql + ' UNION ALL '; SET @asciiValue = @asciiValue + 1; END --PRINT @sql; -- the 'live' view of the active directory data. IF OBJECT_ID('v_ADView') IS NOT NULL DROP VIEW v_ADView EXEC(@sql); -- ADTable holds a 'snapshot' of the active directory data. IF OBJECT_ID('[Users_AD]', 'U') IS NULL SELECT DisplayName, TelephoneNumber AS Phone, Mail, Mobile, FacsimileTelephoneNumber AS Fax INTO [Users_AD] FROM v_ADView; ELSE INSERT INTO [Users_AD] SELECT DisplayName, TelephoneNumber AS Phone, Mail, Mobile, FacsimileTelephoneNumber AS Fax FROM v_ADView; END GO -- By calling Select_LDAP_Rows with a separate character each time, -- build up a table containing all the LDAP data for each user. ALTER PROC [dbo].[Select_LDAP_Rows_Master] AS BEGIN -- ADTable holds a 'snapshot' of the active directory data. IF OBJECT_ID('[AdTable]', 'U') IS NOT NULL DROP TABLE [AdTable]; -- Create a database view from unions of smaller selects. The max 901 records thing in AD forces us to do this. DECLARE @sql VARCHAR(200) DECLARE @asciiValue INT = ASCII('A'); DECLARE @asciiEnd INT = ASCII('Z'); -- Create a view of the active directory data and insert to table Users_AD WHILE @asciiValue <= @asciiEnd BEGIN SET @sql = 'EXEC dbo.Select_LDAP_Rows ' + CHAR(@asciiValue) + ' '; SET @asciiValue = @asciiValue + 1; --PRINT @sql; EXEC(@sql); END END 

Answer 8

I like the union option - this is the best and easiest.

select TOP 901 * from openquery (adsisca, 'select givenName, sn, SamAccountName
from '' LDAP: //10.1.2.3: 389 '' where objectCategory = '' Person '' as well as objectclass = '' InetOrgPerson '' and also sAMAccountName <"M" "") union select TOP 901 * from openquery (adsisca , 'select givenName, sn, SamAccountName
from '' LDAP: //10.1.2.3: 389 '' where objectCategory = '' Person '' as well as objectclass = '' InetOrgPerson '' and also sAMAccountName> = '' m '' ")

Answer 9

I praised how John Sinclair answered so much, I decided on the highest form of flattery - imitation. Here is my execution of his decision. Instead of declaring an ADSI LDAP connection with every request in OpenRowSet, I chose the OpenQuery method:

 DECLARE @DomainFQDN VARCHAR(50) = '<your.domain.FQDN>'; IF OBJECT_ID('tempdb..#ADData') IS NOT NULL DROP TABLE #ADData; -- Query AD for all known user accounts CREATE TABLE #ADData( lanId NVARCHAR(256), firstName NVARCHAR(256), lastName NVARCHAR(256), email NVARCHAR(256), costcenter NVARCHAR(256), --Our AD implementation uses the optional extensionAttributes, defining 1 as cost center mobile NVARCHAR(256), --In @Query below, the name of this column is the same as the LDAP returned parameter, so no equate is applied in the query country NVARCHAR(256), usnCreated BIGINT --uSNCreated is an INT64 object type ); --Define the AD LDAP connection IF NOT EXISTS(SELECT 1 FROM sys.servers WHERE name = 'ADSI') EXEC master.dbo.sp_addlinkedserver @server = N'ADSI', @srvproduct = N'Active Directory Services', @provider = N'ADsDSOObject', @datasrc = @DomainFQDN; DECLARE @Rowcount int; DECLARE @LastCreatedFilter VARCHAR(200) = ''; DECLARE @ADrecordsToReturn smallint = 901; --AD will not return more than 901 records per query (changed from 1000 at some point). You can set it to any value smaller to control the 'pagesize' of returned results --Loop mechanics: -- - 1st loop: @Rowcount will be NULL but we need to looping to commence, thus ISNULL function -- - Intermediate loops: Rowcount will equal the max number of requested records, indicating there may be more to query from AD --SELECT @LastCreatedFilter = 'AND usnCreated = ''''<yourvalue>'''''; --Used during debugging to iniate the loop at a certain value --DECLARE @TestStop int = 1; -- @TestStop is a debug option to halt processing. It needs to be commented in or out at 3 places WHILE ISNULL(@Rowcount,@ADrecordsToReturn) = @ADrecordsToReturn --AND @TestStop < 4 --Un-comment the three @TestStop lines to run a reduced sample query of AD, dictated by the value provided on this line (# of loops to process before stopping) BEGIN DECLARE @Query VARCHAR (2000) = ' SELECT TOP ' + CONVERT(varchar(10),@ADrecordsToReturn) + ' lanId = SamAccountName, firstName = GivenName, lastName = sn, email = mail, bsbcc = extensionAttribute1, mobile, country = c, usnCreated FROM OpenQuery ( ADSI, '' SELECT SamAccountName, GivenName, sn, mail, extensionAttribute1, mobile, c, usnCreated FROM ''''LDAP://' + @DomainFQDN + ''''' WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' ' + @LastCreatedFilter + ' ORDER BY usnCreated '' ) '; INSERT INTO #ADData EXEC (@Query); SELECT @Rowcount = @@ROWCOUNT; SELECT @LastCreatedFilter = 'AND usnCreated > ' + LTRIM(STR((SELECT MAX(usnCreated) FROM #ADData))); --PRINT @LastCreatedFilter; --While debugging, used to determine progress --SET @TestStop = @TestStop + 1; -- @TestStop is a debug option to halt processing. It needs to be commented in or out at 3 places END; EXEC master.dbo.sp_dropserver 'ADSI'; --Do something with the results... SELECT lanId, email, costcenter, mobile, country, usnCreated FROM #ADData order by lanId;