Dynamic SQL OPENQUERY ADSI - Update Table with AD

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dynamic SQL OPENQUERY ADSI - Update Table with AD

Author

Topic

leodone
Starting Member

30 Posts

Posted - 2011-09-20 : 16:01:58

I have an ADSI linked server setup on a SQL 2005 server. I've populated a table, ADUsers with approximately 25,000 Active Directory user account names and associated distinguished name. I've written the following to grab the Active Directory group(s) associated with the distinguished name and determine which group has the highest priority based on a SQL table (UserGroup) that contains the group names and a priority setting. Once I have the group, I'd like to update the ADUsers table so that each user account has the highest priority group associated with it.
When I run the following from SQL Server Management Studio , it updates about 1200 to 1300 rows and then errors out with the following error: An error occurred while preparing the query "
SELECT name
FROM 'LDAP://<server>/DC=<xyz>,DC=<xyz>'
WHERE objectClass = 'group'
AND member = 'CN=John Smith,OU=OMSNIC Portal - Users,DC=<xyz>,DC=<xyz>' " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
After it errors out, I'm able to re-execute the code below and it updates another 1200 to 1300 rows and then errors out again, but on a different row. I'm thinking I'm hitting a limit of some kind....does anyone have any ideas?

SET NOCOUNT on
DECLARE @distinguishedName varchar(255)
DECLARE @distinguishedName2 varchar(255)
DECLARE @Query NVARCHAR(4000)
DECLARE @QuoteIndex int
DECLARE @userID int
Select @userID = min(ADUsersID)
from ADUsers
WHERE UserGroup is null
WHILE @userID IS NOT NULL
BEGIN
SELECT @distinguishedName = ADDistinguishedName
FROM ADUsers
WHERE ADUsersID = @userID
/* take care of surnames with an apostrophe */
SET @distinguishedName2 = @distinguishedName
SELECT @QuoteIndex = CHARINDEX('''', @distinguishedName)
IF (@QuoteIndex > 0)
BEGIN
SET @distinguishedName = REPLACE(@distinguishedName, '''', '''''''''')
SET @distinguishedName2 = REPLACE(@distinguishedName2, '''', '''''')
END

SET @Query = 'UPDATE ADUsers
SET UserGroup = (SELECT UserGroup
FROM OPENQUERY(ADSI,''
SELECT name
FROM ''''LDAP://<server>/DC=<xyz>,DC=<xyz>''''
WHERE objectClass=''''group''''
AND member=''''' + @DistinguishedName + ''''' '')
LEFT OUTER JOIN
UserGroup ON name = UserGroup
WHERE Active = 1
AND Priority = (SELECT Max(Priority)
FROM OPENQUERY(ADSI,''
SELECT name
FROM ''''LDAP://<server>/DC=<xyz>,DC=<xyz>''''
WHERE objectClass = ''''group'''' AND member = ''''' + @DistinguishedName + ''''' '')
LEFT OUTER JOIN UserGroup ON name = UserGroup
WHERE Active = 1))
WHERE ADDistinguishedName = ''' + @distinguishedName2 + ''' '
EXEC SP_EXECUTESQL @Query

SELECT @userID = min(ADUsersID)
FROM ADUsers
WHERE ADUsersID > @userID
AND UserGroup is null

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources