Query of 2 Seperate Active Directories

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 2012 Forums

Transact-SQL (2012)

Query of 2 Seperate Active Directories

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-29 : 14:14:10

I have a query that works, but now I want to add another AD to get the user list from. I would like to know if by looking at this code, if I can combine the results of the 2 active directories before we update the 1 table that is holding the information?

Code:

DECLARE
@adsiQuery NVARCHAR(1000),
@sql NVARCHAR(1000),
@alpha TINYINT

--DECLARE @TmpUsers TABLE (
-- userPrincipalName varchar(100) ,
-- givenName varchar(100) ,
-- cn varchar(100) ,
-- DisplayName varchar(100) ,
-- title varchar(100) ,
-- sAMAccountName varchar(100) ,
-- distinguishedName varchar(100),
-- Email varchar(100),
-- ManualAdd bit);

SELECT * FROM OpenQuery
(
ADSI,
'SELECT userPrincipalName, givenName, cn, DisplayName, title, sAMAccountName,
distinguishedName,mail
FROM ''LDAP://jhcohn.com/OU=Players,DC=jhcohn,DC=com''
WHERE objectClass = ''User''
') AS tblADSI
WHERE Left(Title,1) = 'P'
ORDER BY displayname

USE NCOS_Dev

-- This is the actual merge area. I would love to have both directories in the "tblADSI" at this point.

MERGE NCOS_DomainUser as stm
USING (SELECT DISTINCT DisplayName,mail,Title FROM tblADSI) as sd
ON (stm.NC_DisplayName = sd.displayname)
WHEN MATCHED AND stm.NC_Manual_Add <> '1' THEN UPDATE SET stm.NC_Email = sd.mail, stm.nc_title = sd.title,stm.nc_manual_add = 'False'
WHEN NOT MATCHED THEN
INSERT(NC_DisplayName,NC_Email,NC_Title,NC_Domain)
VALUES(sd.displayName,sd.mail,sd.Title,'JHC');
GO

Any thoughts

Thanks

Bryan Holmstrom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources