RE: SQL ADSI Query limitation

SSC Rookie

Points: 29

October 6, 2017 at 12:26 pm

#1962694

Brian ODwyer - Thursday, February 9, 2012 2:54 PM
Definite 255 char limit. See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65584I used the function to get around and loop throughdeclare @Apptemp3 table (BSamName varchar(255),BDisplayName varchar(255),BPhone varchar(100),BEMail varchar(255),BFirstName varchar(100),BLastName varchar(100))DECLARE @sChar char(1)declare @body varchar(8000)DECLARE @nAsciiValue smallintSELECT @nAsciiValue = 65WHILE @nAsciiValue < 91 BEGIN SELECT @sChar= CHAR(@nAsciiValue)SET @body = (select dbo.fnSprintf('SELECT sAMAccountName, displayName, mail, telephoneNumber, sn, givenName FROM OPENQUERY( ADSI, ''SELECT displayName, sAMAccountName, mail, telephoneNumber, sn, givenName FROM ''''LDAP://dc=cchcs,dc=ldap''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )', @SChar +'*', default)) INSERT @Apptemp3 EXEC( @body) SELECT @nAsciiValue = @nAsciiValue + 1 ENDSelect ISNULL(Upper(BSamName),'NoSamID'),ISNULL(BDisplayName,'NoDName'),ISNULL(BPhone,'NoPhone'),ISNULL(BEMail,'Nomail'),ISNULL(BFirstName,'NoFname'),ISNULL(BLastName,'NoLname') from @Apptemp3order by BSamName

The above example was great but I ran into an issue where there were more than 1000 's' names and even more than 1000 service accounts with 's.*' naming format.

While not elegant my script below loops through the different parts of the alphabet and handles s. Hope this helps somebody else.

--Hitting row limitations at 's' > chokes on services

DROP TABLE #tmpADUsers;

CREATE TABLE #tmpADUsers
(
employeeId VARCHAR(10) NULL,
SAMAccountName VARCHAR(255) NOT NULL,
givenname VARCHAR(255) NULL,
sn VARCHAR(255) NULL,
email VARCHAR(255) NULL,
CN VARCHAR(255) NULL
);

GO
-- loop through the alphabet from A-R

DECLARE @cmdstr VARCHAR(255);
DECLARE @nAsciiValue SMALLINT;
DECLARE @sChar CHAR(1);

SELECT @nAsciiValue = 65;

WHILE @nAsciiValue < 83
BEGIN

SELECT @sChar = CHAR(@nAsciiValue);

EXEC master..xp_sprintf @cmdstr OUTPUT,
'SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
FROM OPENQUERY( ADSI, ''SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
FROM ''''LDAP://DC=corp,DC=symetra,DC=com''''WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''%s*'''''' )',
@sChar;

INSERT #tmpADUsers
EXEC (@cmdstr);

SELECT @nAsciiValue = @nAsciiValue + 1;
END;

-- loop through the alphabet from T-Z
GO
DECLARE @cmdstr VARCHAR(255);
DECLARE @nAsciiValue SMALLINT;
DECLARE @sChar CHAR(1);

SELECT @nAsciiValue = 84;

WHILE @nAsciiValue < 91
BEGIN

SELECT @sChar = CHAR(@nAsciiValue);

INSERT #tmpADUsers
EXEC (@cmdstr);

SELECT @nAsciiValue = @nAsciiValue + 1;
END;

-- pull in s followed by an alpha
GO
DECLARE @cmdstr VARCHAR(255);
DECLARE @nAsciiValue SMALLINT;
DECLARE @sChar CHAR(1);

SELECT @nAsciiValue = 84;

WHILE @nAsciiValue < 91
BEGIN

SELECT @sChar = CHAR(@nAsciiValue);

INSERT #tmpADUsers
EXEC (@cmdstr);

SELECT @nAsciiValue = @nAsciiValue + 1;
END;

GO
--this pulls in s with periods as the 2nd character
DECLARE @cmdstr VARCHAR(255);
DECLARE @nAsciiValue SMALLINT;
DECLARE @sChar CHAR(1);

SELECT @nAsciiValue = 65;

WHILE @nAsciiValue < 91
BEGIN

SELECT @sChar = CHAR(@nAsciiValue);

EXEC master..xp_sprintf @cmdstr OUTPUT,
'SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
FROM OPENQUERY( ADSI, ''SELECT employeeId, SAMAccountName, givenname, sn, Mail, CN
FROM ''''LDAP://DC=corp,DC=symetra,DC=com'''' WHERE objectCategory = ''''Person'''' AND SAMAccountName = ''''s.%s*'''''' )',
@sChar;

INSERT #tmpADUsers
EXEC (@cmdstr);

SELECT @nAsciiValue = @nAsciiValue + 1;
END;

SELECT *
FROM #tmpADUsers
ORDER BY SAMAccountName;