SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Query of 2 Seperate Active Directories
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
69 Posts

Posted - 03/29/2013 :  14:14:10  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000