I tend to need queries that tie a lot of sources together.  Although Linked Servers are good for this (with caveats), I often run into times where a Linked Server isn’t the perfect fit.  Active Directory is one of those problematic treasure hunts.  There are a lot of good resources out on the internet, but they don’t take into account the totality of the issue.

This past week, I needed to pull data into a job whereby T-SQL was required to grab the data from AD and incorporate it as part of the job.  However, there was a problem.  Whenever I would run either an OPENROWSET or OPENQUERY on our Microsoft SQL Server 2012 server, it would give me an error after 19,901 rows no matter what I did.  There were plenty of resources referring to 2005 and 2008, but none specific to 2012.  In the older versions, the resources showed a limit of 901 objects only being returned.  This has improved in 2012 and I cannot speak to 2014 or 2016 yet.  However, there wasn’t a definitive way of pulling back all the data into a single query or no real reference on what to do.

My solution isn’t perfectly elegant, but it does get the job done.  If anyone has a less “codey” way of handling it, I would love to hear from you.  What I did was create a temporary table and a simple table expression to hold the data and the letters of the alphabet (in this case I was looking at users).  The query should still work if you change from the user class to computer or groups.  You will only need to change the fields returned and the WHERE clause.   Essentially, we start by creating a temporary table for the data called #ADTable, followed by a table expression called @letterList that has the alphabet inserted into it.  We then declare a Cursor, pull in the first letter and use it as a variable in the insert query variable below.  This loops through each letter, populating #ADTable until there are no more letters.  We can then display all the information or do whatever we need!

I will show both the OPENROWSET and OPENQUERY functions below:

OPENROWSET –

IF OBJECT_ID(‘TEMPDB..#ADTable’) IS NOT NULL DROP TABLE ADTable;
CREATE TABLE #ADTable(
sAMAccountName nvarchar(255),
sn nvarchar(255),
givenName nvarchar(255),
displayName nvarchar(255),
objectGUID varbinary(max)
)
DECLARE @Letter CHAR(1)
DECLARE @letterList TABLE(letter CHAR(1));
DECLARE @sql nvarchar(max)

INSERT INTO @letterList
VALUES(‘a’),(‘b’),(‘c’),(‘d’),(‘e’),(‘f’),(‘g’),(‘h’),(‘i’),
(‘j’),(‘k’),(‘l’),(‘m’),(‘n’),(‘o’),(‘p’),(‘q’),(‘r’),(‘s’),
(‘t’),(‘u’),(‘v’),(‘w’),(‘x’),(‘y’),(‘z’)
DECLARE Letter_Cursor CURSOR FOR SELECT letter FROM @letterList;
OPEN Letter_Cursor;
FETCH NEXT FROM Letter_Cursor INTO @Letter;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘INSERT INTO #ADTable (sAMAccountName, sn, givenName, displayName, objectGUID) SELECT TOP 19901 sAMAccountName, sn, givenName, displayName, objectGUID FROM OPENROWSET(
”ADSDSOOBJECT”,”User ID={AD Username};Password={AD Password};ADSI Flag=0x11;Page Size=70000”,
”SELECT sAMAccountName, sn, givenName, displayName, objectGUID
FROM ””LDAP://{your.domain}/ou={Some Org Unit},dc={Your},dc={Domain}””
WHERE objectClass=””User”” AND givenName = ‘ + ””” + @Letter + ‘*”” AND sn = ””*”” AND company = ””*”” ”) ‘
EXEC(@sql)
FETCH NEXT FROM Letter_Cursor INTO @Letter;
END;
CLOSE Letter_Cursor;
DEALLOCATE Letter_Cursor;

SELECT sAMAccountName, givenName, sn, displayName, CONVERT(NVARCHAR(MAX), objectGUID, 1) AS objectGUID FROM #ADTable;
DROP TABLE #ADTable

OPENQUERY –

IF OBJECT_ID(‘TEMPDB..#ADTable’) IS NOT NULL DROP TABLE #ADTable;
CREATE TABLE #ADTable(
sAMAccountName nvarchar(255),
sn nvarchar(255),
givenName nvarchar(255),
displayName nvarchar(255),
objectGUID varbinary(max)
)
DECLARE @Letter CHAR(1)
DECLARE @letterList TABLE(letter CHAR(1))
DECLARE @sql nvarchar(max)

INSERT INTO @letterList
VALUES(‘a’),(‘b’),(‘c’),(‘d’),(‘e’),(‘f’),(‘g’),(‘h’),(‘i’),
(‘j’),(‘k’),(‘l’),(‘m’),(‘n’),(‘o’),(‘p’),(‘q’),(‘r’),(‘s’),
(‘t’),(‘u’),(‘v’),(‘w’),(‘x’),(‘y’),(‘z’)

DECLARE Letter_Cursor CURSOR FOR SELECT letter FROM @letterList
OPEN Letter_Cursor
FETCH NEXT FROM Letter_Cursor INTO @Letter
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = ‘INSERT INTO #ADTable (sAMAccountName, sn, givenName, displayName,
objectGUID) SELECT TOP 19901 sAMAccountName, sn, givenName, displayName,
objectGUID FROM OPENQUERY(
ADSI,
”SELECT sAMAccountName, sn, givenName, displayName, objectGUID
FROM ””LDAP://{your.domain}/ou={Your ORG Unit},dc={Your},dc={Domain}””
WHERE objectClass=””User”” AND givenName = ‘ + ””” + @Letter + ‘*”” ”) ‘
EXEC(@sql)
FETCH NEXT FROM Letter_Cursor INTO @Letter
END
CLOSE Letter_Cursor
DEALLOCATE Letter_Cursor

SELECT sAMAccountName, givenName, sn, displayName, CONVERT(NVARCHAR(MAX), objectGUID,
1) AS objectGUID FROM #ADTable

Note that you can add, subtract, or change the fields in the select statements, but you will need to make sure you do this in two places in the @SQL expression and the select at the bottom.  Also, I used single quotes for everything so when you see ”” that is four single quotes.  I hope this helps!