Exécution d'une requête UPDATE dans une variable

Version imprimable

Bonjour,
J'ai la procédure ci-dessous, qui va chercher des infos dans l'AD et qui les stock ensuite dans une table temporaire pour un traitement ultérieur.
Pour l'insertion des données, cela fonctionne bien, mais je souhaite faire un UPDATE également de temps en temps. Le truc c'est que je ne sais pas comment faire un update lorsqu'on utilise un EXEC(@myquery). CF. la partie 3.2, en rouge, de mon code. J'ai parcouru internet et pas trouvé d'exemple allant dans mon sens. Alors ce n'est peut être pas possible avec ce code, mais pouvez-vous me le confirmer et me dire comment est-ce que je devrais faire ?

Code:

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

Bonjour,

Vous ne pouvez pas utiliser les résultat d'un exec comme ceci. Tout au plus, vous pouvez insérer le résultat dans une table, mais pas l'utiliser comme une pseudo table.

Vous povuez envisager d'inserer le tous vos résultat dans une table temporaire, et faire un MERGE au final.

Voire directement faire une MERGE en utilisant les openquery comme source.

Quelque chose dans ce genre, sans doute a adapter :

Code:

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
 
ALTER  PROCEDURE [ITS_selectLdapUsers]
 
AS 
BEGIN 
 
      DECLARE @adspath              AS nvarchar(1000) 
 
select @adspath = '<LDAP://DC=root,DC=myserveur,DC=myextension>' 
 
SET NOCOUNT ON;
 
      -- 3.2 Insert / update data into gobal temp database 
MERGE em_temp c
USING (
	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,'<LDAP://DC=root,DC=myserveur,DC=myextension>;(&(objectCategory=person)  (objectClass=user) (givenname=*)  );SAMAccountName;subtree')
	CROSS APPLY openquery (ADSI, @adspath+';(&(objectCategory=person)  (objectClass=user)(SAMAccountName='+SAMAccountName+') );SAMAccountName,givenName,sn,mail,ADsPath;subtree') 
) s
	ON s.em_id = c.em_id
WHEN MATCHED THEN 
	  UPDATE 
             SET name_first=src.name_first
             ,  name_last=src.name_last
             , email=src.email
WHEN NOT MATCHED BY TARGET THEN  
	INSERT ( 
            em_id 
            ,name_first 
            ,name_last 
            ,email 
			,OU ) 
	VALUES ( 
            src.em_id 
            ,src.name_first 
            ,src.name_last 
            ,src.email 
			,src.OU 
		)
 
;
END

24/10/2014, 11h27
Fritzoune

Merci pour votre réponse. Etant un gros néophyte, je vais étudier cette fonction MERGE.

La j'insère effectivement les données dans une table temporaire. Je me dit que de faire un update si les données sont déjà existantes dans cette table temporaire par rapport à l'AD, ca peut me faire gagner du temps de traitement par rapport à un delete / insert à chaque exécution de la procédure.

Hello,
Voici ce que j'ai modifié et ca me donne un premier résultat prometteur.
Code:

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 ALTER PROCEDURE [ITS_selectLdapUsers] AS BEGIN SET NOCOUNT ON; -- 3.2 Insert / update data into gobal temp database MERGE em_temp c USING ( SELECT SAMAccountName as em_id ,givenname AS name_first ,sn AS name_last ,mail AS email ,[net-user-name] AS net_user_name ,SUBSTRING(department,0,16) as dp_id ,ADsPath AS OU --,SUBSTRING(ADsPath,LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)+1)),LEN(ADsPath)-LEN(RIGHT(ADsPath,20))-LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)))) AS OU FROM OPENQUERY(ADSI,'<LDAP://root.blabla:3268/DC=root,DC=blabla,DC=blibli>;(&(objectCategory=person) (objectClass=user) (givenname=f*) );SAMAccountName,givenName,sn,mail,net-user-name,department,ADsPath;subtree') ) s ON s.em_id COLLATE DATABASE_DEFAULT = c.em_id COLLATE DATABASE_DEFAULT WHEN MATCHED THEN UPDATE SET name_first=s.name_first , name_last=s.name_last , email=s.email ,net_user_name = s.net_user_name ,dp_id=s.dp_id ,OU =s.ou WHEN NOT MATCHED BY TARGET THEN INSERT ( em_id ,name_first ,name_last ,email ,net_user_name ,dp_id ,ou ) VALUES ( s.em_id ,s.name_first ,s.name_last ,s.email ,s.net_user_name ,s.dp_id ,s.ou ) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, Inserted.*, Deleted.*; END
et donc merci infiniment :D
Mais pour le coup je me retrouve avec 2 nouvelles difficultés:

l'utilisation des variables..
et ma fonction substring qui ne marche plus

Le plus important:

Code:

(givenname=f*)

Comme je vais piocher dans l'AD; et qu'on est limité par le nb d'enregistrement retournés, je comptais utiliser cet attribut pour les filtres. Et en faire une variable, affectée via l'appelle de ma procédure. -->givenname=+@mavar
Dans mon premier cas, j'ai mis toute la partie de la requête dans une variable adsiquery, alors ca fonctionnait. Ici je ne peux pas mettre de variable directement dans l'openquery.
Quelle serait la marche à suivre ?
J'ai essayé de mettre toute l'instruction SQL dans une variable adsiquery , mais lors de l'exec le système dit qu'il ne prend pas en charge MERGE. Je ne comprends pas pourquoi.

Et c'est aussi lié avec ma ligne en commentaire ici:

Code:

SUBSTRING(ADsPath,LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)+1)),LEN(ADsPath)-LEN(RIGHT(ADsPath,20))-LEN(LEFT(ADsPath,CHARINDEX('','',ADsPath)))) AS OU

Lorsqu'elle était dans la variable adsiquery , ca fonctionnait, et maintenant ,j'ai une erreur comme quoi charindex n'aime pas les varchar.
mais bon ca c'est moins important. Ca m'affiche juste l'OU d'une façon plus lisible.