AdsiPagedQuery.sql

--before running the below code, ensure you've set up a linked server per notes here: https://blog.sqlauthority.com/2016/03/30/sql-server-query-active-directory-data-using-adsi-ldap-linked-server/

declare @pageSize bigint = 900
, @usnMax bigint = 0
, @usnMaxPrevious bigint = -1
, @ldapPath nvarchar(max) = 'LDAP://myDomain.myForest.myCompany.com/DC=myDomain,DC=myForest,DC=myCompany,DC=com'
, @columnList nvarchar(max)
, @dynamicSql nvarchar(max)
, @dynamicSqlTemplate nvarchar(max) = '
INSERT INTO #PagedAdsi (@columnList)
SELECT TOP @pageSize @columnList 
FROM OpenQuery (
    ADSI,
    ''SELECT @columnList
    FROM ''''@ldapPath''''
    WHERE usnCreated > @usnMax
    AND objectClass = ''''User''''
    ORDER BY usnCreated
'')
'
--NB: Only use columns in this table which relate to fields in AD; ensure the field names match
create table #PagedAdsi 
(
    usnCreated bigint not null primary key clustered
    ,displayname nvarchar(256)
    ,sAMAccountName nvarchar(256)
)
--we use the columns in the temp table to determine what we pull from LDAP; so we only need to define them in one place
select @columnList = coalesce(@columnList + ', ', '') + name from tempdb.sys.columns where object_id = object_id('tempdb..#PagedAdsi')

--we only need to set some properties once, since they won't change between iterations.  As such, here we update the template itself
set @dynamicSqlTemplate = replace(@dynamicSqlTemplate, '@ldapPath', @ldapPath)
set @dynamicSqlTemplate = replace(@dynamicSqlTemplate, '@columnList', @columnList)
set @dynamicSqlTemplate = replace(@dynamicSqlTemplate, '@pageSize', cast(@pageSize as nvarchar(9)))

while (@usnMax > @usnMaxPrevious)
begin
    --change the usnMax each iteration, to move on to the next page
    set @dynamicSql = replace(@dynamicSqlTemplate, '@usnMax', cast(@usnMax as nvarchar(9)))
    print @dynamicSql --useful for debugging
    exec (@dynamicSql)
    set @usnMaxPrevious = @usnMax
    select @usnMax = max(usnCreated) from #PagedAdsi
END

--show how many records we've read to prove that our paging solution worked
select count(1) RecordsRetrieved 
from #PagedAdsi 

drop table #PagedAdsi