Retrieve >901 rows from SQL Server 2008 linked ser

问题:

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

This query:

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

works.

However changing the query and trying to retrieve 902 rows does not :

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

The error is:

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

I've found other instances of people discussing the same problem on forums and they never fixed it, just worked around it writing multiple views and union'ing them together for example.

Is there a more elegant fix, is there a setting I can change somewhere to retrieve more than 901 rows?

回答1:

Use union to circumvent 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''
')

回答2:

I know this is an old post, but I too had the same issues, and examined the proposed solution above. (Basically using a bunch of smaller selects with a changing criteria to keep the row count down) I just cut a slightly different version of it, and unioned them all into a Db View. I couldn't be bothered with that MaxPageSize thing - it looks too much effort.

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]

回答3:

The problem

The error is:

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

I've found other instances of people discussing the same problem on forums and they never fixed it, >just worked around it writing multiple views and union'ing them together for example.

Is there a more elegant fix, is there a setting I can change somewhere to retrieve more than 901 >rows?

The solution

I just solved the same problem faced by me, without going for any Active Directory setting changes (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.

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)

回答4:

I didn't like the smell of other options posted here that much, 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

回答5:

You need to change the MaxPageSize setting in Active Directory. To do that you need to use Ntdsutil.exe which you can type on the run command then follow these steps

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

回答6:

I need to change the MaxTempTableSize setting in Active Directory. To do that you need to use Ntdsutil.exe which you can type on the run command 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

回答7:

This version of the solution deals with situations where the number of users beginning with the specified character is still > 901. It uses a master procedure calling 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

回答8:

I like the union option is the best and simple.

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

回答9:

I appreciated John Sinclair's answer so much, I've decided on the highest form of flattery - imitation. Here's my rendition of his solution. Instead of declaring the ADSI LDAP connection with every query in the OpenRowSet, I've opted for 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;