1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73
| ALTER PROCEDURE [ITS_selectLdapUsers]
AS
BEGIN
DECLARE @userName AS NVARCHAR(50)
DECLARE @adsiquery AS nvarchar(4000)
DECLARE @adspath AS nvarchar(1000)
select @adspath = '<LDAP://DC=root,DC=myserveur,DC=myextension>'
SET NOCOUNT ON;
-- 1. Get all the staff members
DECLARE rs CURSOR FOR
SELECT SAMAccountName
FROM OPENQUERY(ADSI,'<LDAP://DC=root,DC=myserveur,DC=myextension>;(&(objectCategory=person) (objectClass=user) (givenname=*) );SAMAccountName;subtree')
ORDER BY SAMAccountName
OPEN rs
-- 2. Get first value
FETCH NEXT FROM rs INTO @userName
-- 3. Start the loop for all employee department members
WHILE @@FETCH_STATUS = 0
BEGIN
-- 3.1 Read data from LDAP
set @adsiquery='(
SELECT
SAMAccountName as em_id
,givenname AS name_first
,sn AS name_last
,mail AS email
,SUBSTRING(ADsPath,LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)+1)),LEN(ADsPath)-LEN(RIGHT(ADsPath,20))-LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)))) AS OU
FROM openquery
(ADSI, '''+@adspath+';(&(objectCategory=person) (objectClass=user)(SAMAccountName='+@userName+') );SAMAccountName,givenName,sn,mail,ADsPath;subtree'')
)'
-- 3.2 Insert / update data into gobal temp database
IF NOT EXISTS (SELECT 1 FROM em_temp WHERE em_id = @userName)
BEGIN
INSERT INTO em_temp (
em_id
,name_first
,name_last
,email
,OU )
EXEC (@adsiquery)
END
ELSE
BEGIN
UPDATE em_temp
SET name_first=????(givenname), name_last=????(sn), email=???(mail)
WHERE em_id=@userName
EXEC (@adsiquery)
END
FETCH NEXT FROM rs INTO @userName
END
CLOSE rs
DEALLOCATE rs
END |
Partager