Sql server 2000 DROP et Transaction

Version imprimable

Bonjour,

J'ai un petit souci au niveau des drop de colonne d'une table et des transactions. En effet, je desire creer une table T1 a partir d'une autre table T2. Dans T1, je copie une colone de T2 et j efface cette colonne de T2. Je effacer la colonne que si la copie a fonctionne. Je desire donc creer une transaction pour la copie. Si elle a reussi, j efface la colonne.
Voici le code:

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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
 
BEGIN TRANSACTION
BEGIN TRANSACTION
CREATE TABLE [DIN] (
	[Id] [bigint] NOT NULL ,
	[DIN_Num] [varchar] (20) COLLATE French_CI_AS NOT NULL ,
	[Zge_Id] [bigint] NULL,
	[Ordre] [int] NOT NULL ,
	CONSTRAINT [PK_DIN] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	CONSTRAINT [FK_DIN_ZoneGeo] FOREIGN KEY 
	(
		[Zge_Id]
	) REFERENCES [ZoneGeo] (
		[Id]
	) 
) ON [PRIMARY]
--GO
 
 
exec sp_addextendedproperty N'MS_Description', N'Dialogue de discordance d’appareils à position habituelle affecté à la zone géographique ', N'user', N'dbo', N'table', N'DIN'
 
--GO
 
exec sp_addextendedproperty N'MS_Description', N'Identifiant unique au PT', N'user', N'dbo', N'table', N'DIN', N'column', N'Id'
--GO
exec sp_addextendedproperty N'MS_Description', N'Numéro du dialogue DIN', N'user', N'dbo', N'table', N'DIN', N'column', N'DIN_Num'
--GO
exec sp_addextendedproperty N'MS_Description', N'Zone géographique de rattachement du dialogue DIN', N'user', N'dbo', N'table', N'DIN', N'column', N'Zge_Id'
--GO
exec sp_addextendedproperty N'MS_Description', N'Ordre de classement des DIN', N'user', N'dbo', N'table', N'DIN', N'column', N'Ordre'
 
 
--GO
 
-- on renseigne la nouvelle table avec les DIN déjà saisis
DECLARE @DIN_Num sysname
DECLARE @Id_Zge bigint
DECLARE @Zge_Num sysname
DECLARE @cpt bigint
DECLARE @ordre int
DECLARE @pt sysname
 
SET @pt=(select db_name())
SET @ordre = 1
 
DECLARE C CURSOR FOR SELECT Id, Zge_Num, Zge_DINNum 
FROM  ZoneGeo
WHERE Zge_DIN = '1'
OPEN C
FETCH NEXT FROM C INTO @Id_Zge, @Zge_Num, @DIN_Num
WHILE @@FETCH_STATUS = 0
BEGIN
	-- lecture du compteur d'id (+1)	
	SET @cpt=(SELECT Max(Pl_Compteur)+1 FROM POSTES..PlanT 	
	WHERE Id = (SELECT In_Pl_Id FROM POSTES..Indice WHERE In_Bd_Pt = @pt))
 
	--si on tombe sur plusieurs DIN identiques ou aucun nuléro de DIN
	IF @DIN_NUM IS NULL
	BEGIN
		SET @DIN_NUM = '9999_' +@Zge_Num 
	END
	ELSE IF EXISTS(SELECT DIN_Num FROM DIN WHERE DIN_Num=@DIN_Num)
	BEGIN
		SET @DIN_Num = @DIN_Num +'_' +@Zge_Num
	END
 
	INSERT INTO DIN (Id, DIN_Num, Zge_Id, Ordre) VALUES (@cpt, @DIN_Num, @Id_Zge, @ordre)
 
	-- on met à jour les renvois
--	UPDATE Renvoi
--	SET Renv_Table = 'DIN', Renv_Col = 'DIN_Num', Renv_Cle1 = @cpt	
--	WHERE Renv_Table = 'ZoneGeo' AND Renv_Col = 'Zge_DINNum' AND Renv_Cle1=@Id_Zge
 
	SET @ordre=@ordre+1
 
	-- mise à jour du compteur
	UPDATE POSTES..PlanT SET Pl_Compteur = @cpt 
	WHERE Id =(SELECT In_Pl_Id FROM POSTES..Indice WHERE In_Bd_Pt = @pt)
 
	FETCH NEXT FROM C INTO @Id_Zge, @Zge_Num, @DIN_Num
 
 
 
END
CLOSE C
DEALLOCATE C
COMMIT
 
ALTER TABLE dbo.ZoneGeo
	DROP COLUMN Zge_DIN
ALTER TABLE dbo.ZoneGeo
	DROP COLUMN Zge_DINNum

Le probleme est que ca ne fonctionne pas. j obtient le message d erreur m'indiquant que la colonne Zge_DIN n existe pas, en pointant sur la declaration du curseur.

Comment faire????

Merci d'avance.

03/08/2006, 07h19
Fabien Celaia

Le faire en 2 SP différentes

Au délà de ta question, je me demande pourquoi tu utilises un curseur ?

En rempliisant ta table DIN avec un simple 'select' sur ta table ZoneGEO ca passe (en faisant un UNION pour gérer le cas où Zge_DINNum est null ou ps)

Ensuite tu mets à jour via un UPDATE la colonne ORDRE ou mieux, tu déclares cette colonne en identity(x,y) ce qui fait qu'elle va s'auto-incrémenter à chaque insertion

Bref, un ordre SQL du genre : (en supposant que les colonnes ORDRE et ID sontde type IDENTITY)
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 SET @cpt=(SELECT Max(Pl_Compteur) FROM POSTES..PlanT WHERE Id = (SELECT In_Pl_Id FROM POSTES..Indice WHERE In_Bd_Pt = @pt)) DECLARE @SQL nvarchar(4000) SET @SQL = ' CREATE TABLE [DIN] ( [Id] bigint IDENTITY (' + convert(nvarchar(4000),@cpt) + 'NOT NULL , [DIN_Num] [varchar] (20) COLLATE French_CI_AS NOT NULL , [Zge_Id] [bigint] NULL, [Ordre] [int] IDENTITY(1,1) NOT NULL ' exec(@sql) INSERT INTO DIN( [DIN_Num], [Zge_Id]) SELECT '9999_' + Zge_Num, Id FROM ZoneGeo WHERE Zge_DIN = '1' AND Zge_DINNum is null UNION ALL SELECT Zge_DINNum + Zge_Num, Id FROM ZoneGeo WHERE Zge_DIN = '1' and Zge_DINNum is not null ALTER TABLE dbo.ZoneGeo DROP COLUMN Zge_DIN ALTER TABLE dbo.ZoneGeo DROP COLUMN Zge_DINNum

03/08/2006, 09h36
fdatdev06

D'apres ce que j ai compris, le fait de faire une transaction permet en cas d erreur de revenir à l etat initial de la base. Je voulais donc faire une transaction pour la creation et la copie de la table et de ces donnees et dans le cas d'une reussite, seulement dans ce cas, effacer les colonnes concernees.
Mais ce ne fonctionne pas.

Je veux bien utiliser deux ps mais comment savoir si elles oont reussis et en cas d erreur comment revenir à letat initial.
03/08/2006, 10h01
ZERS

il suffit d'entourer le code que je t'ai envoyer avec un BEGIN TRANS/ COMMIT TRANS et de tester la variable @@ERROR avant de supprimer tes colonnes.

Je ne vous pas où se situe le problème
03/08/2006, 10h29
fdatdev06

Désolé si je parait un peu lent mais je debute avec sql et sql server.
La colonne ordre doit etre modifiable par logiciel (pour avoir un ordre different). Je n ai pas acces a cette partie du code donc j imagine tres bien une instruction qui change la valeur de ordre et il se peut que par moment il y ait deux fois la meme valeur donc identity ne fonctionne pas. Voila pourquoi j utilise un curseur.
Avec l utilisation du curseur, dans mon code, je recois ce message d erreur:

Server: Msg 207, Level 16, State 3, Line 47
Invalid column name 'Zge_DIN'.

et quand je double clic dessus ca m envoie a la declaration du curseur.
Ca veut dire que la colonne a ete effacee et sqlserver veut encore l utiliser dans le curseur.
Comment faire et dites moi si j ai tout faut.
merci
03/08/2006, 13h26
ZERS

File nous un exemple numérique de ce que tu veux faire, ca ira plus vite ;)
03/08/2006, 13h52
SQLpro

Vous ne pouvez pas faire cela en SQL statique vous devez utiliser du SQL dynamique. L'erreur vient du fait qu'à la compilation cette colonn n'existant pas le compilateur pense que vous avez fait une erreur de syntaxe dans le texte de votre requête.

Il est possible d'utiliser du SQL dynamique même pour un curseur.

A +

Ok, alors j' ai une table Zone_Geo:

ZoneGeo
Id Zge_Num Zge_DIN Zge_DINNum
1 50 0
2 51 1 2222
3 52 1
4 53 1 2222
5 54 1 5555
6 55 1 7777
7 56 1 7777

Je veux avoir une table DIN:
DIN
Id DIN_Num Zge_Id Ordre
3 9999_50 1 1
4 2222_51 2 2
5 9999_52 3 3
6 2222_53 4 4
7 5555 5 5
8 7777_55 6 6
9 7777_56 7 7

La colonne ordre doit etre manipulable par logiciel et contenir plusieur fois la meme valeur.

voici mon code: (qui ne fonctionne pas, voir message precedent)

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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
BEGIN TRANSACTION 
CREATE TABLE [DIN] (
	[Id] [bigint] NOT NULL ,
	[DIN_Num] [varchar] (20) COLLATE French_CI_AS NOT NULL ,
	[Zge_Id] [bigint] NULL,
	[Ordre] [int] NOT NULL ,
	CONSTRAINT [PK_DIN] PRIMARY KEY  CLUSTERED 
	(
		[Id]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] ,
	CONSTRAINT [FK_DIN_ZoneGeo] FOREIGN KEY 
	(
		[Zge_Id]
	) REFERENCES [ZoneGeo] (
		[Id]
	) 
) ON [PRIMARY]
--GO
 
 
exec sp_addextendedproperty N'MS_Description', N'Dialogue de discordance d’appareils à position habituelle affecté à la zone géographique ', N'user', N'dbo', N'table', N'DIN'
 
--GO
 
exec sp_addextendedproperty N'MS_Description', N'Identifiant unique au PT', N'user', N'dbo', N'table', N'DIN', N'column', N'Id'
--GO
exec sp_addextendedproperty N'MS_Description', N'Numéro du dialogue DIN', N'user', N'dbo', N'table', N'DIN', N'column', N'DIN_Num'
--GO
exec sp_addextendedproperty N'MS_Description', N'Zone géographique de rattachement du dialogue DIN', N'user', N'dbo', N'table', N'DIN', N'column', N'Zge_Id'
--GO
exec sp_addextendedproperty N'MS_Description', N'Ordre de classement des DIN', N'user', N'dbo', N'table', N'DIN', N'column', N'Ordre'
 
 
--GO
 
-- on renseigne la nouvelle table avec les DIN déjà saisis
DECLARE @DIN_Num sysname
DECLARE @Id_Zge bigint
DECLARE @Zge_Num sysname
DECLARE @cpt bigint
DECLARE @ordre int
DECLARE @pt sysname
 
SET @pt=(select db_name())
SET @ordre = 1
 
DECLARE C CURSOR FOR SELECT Id, Zge_Num, Zge_DINNum 
FROM  ZoneGeo
WHERE Zge_DIN = '1'
OPEN C
FETCH NEXT FROM C INTO @Id_Zge, @Zge_Num, @DIN_Num
WHILE @@FETCH_STATUS = 0
BEGIN
	-- lecture du compteur d'id (+1)	
	SET @cpt=(SELECT Max(Pl_Compteur)+1 FROM POSTES..PlanT 	
	WHERE Id = (SELECT In_Pl_Id FROM POSTES..Indice WHERE In_Bd_Pt = @pt))
 
	--si on tombe sur plusieurs DIN identiques ou aucun nuléro de DIN
	IF @DIN_NUM IS NULL
	BEGIN
		SET @DIN_NUM = '9999_' +@Zge_Num 
	END
	ELSE IF EXISTS(SELECT DIN_Num FROM DIN WHERE DIN_Num=@DIN_Num)
	BEGIN
		SET @DIN_Num = @DIN_Num +'_' +@Zge_Num
	END
 
	INSERT INTO DIN (Id, DIN_Num, Zge_Id, Ordre) VALUES (@cpt, @DIN_Num, @Id_Zge, @ordre)
 
	-- on met à jour les renvois
--	UPDATE Renvoi
--	SET Renv_Table = 'DIN', Renv_Col = 'DIN_Num', Renv_Cle1 = @cpt	
--	WHERE Renv_Table = 'ZoneGeo' AND Renv_Col = 'Zge_DINNum' AND Renv_Cle1=@Id_Zge
 
	SET @ordre=@ordre+1
 
	-- mise à jour du compteur
	UPDATE POSTES..PlanT SET Pl_Compteur = @cpt 
	WHERE Id =(SELECT In_Pl_Id FROM POSTES..Indice WHERE In_Bd_Pt = @pt)
 
	FETCH NEXT FROM C INTO @Id_Zge, @Zge_Num, @DIN_Num
 
 
 
END
CLOSE C
DEALLOCATE C
IF @@ERROR = 0
BEGIN
	ALTER TABLE dbo.ZoneGeo
		DROP COLUMN Zge_DIN
	ALTER TABLE dbo.ZoneGeo
		DROP COLUMN Zge_DINNum
END
 
COMMIT TRANSACTION

03/08/2006, 16h33
fdatdev06

C est quoi le sql dynamique???

Citation:

Envoyé par fdatdev06

C est quoi le sql dynamique???

c'est lorsque tu génères du code SQL avec des paramètres, dynamiquement quoi
exemple :
Code:

1 2 3 4 5 6 7 8 declare @SQLDYNAMIQUE nvarchar(4000) SET @SQL = 'SELECT * FROM maTable where theChamp = ' set @SQL = @SQL + @unparametre SET @SQL = @SQL + 'AND AutreChamps = ' + @unAutreparametre exec (@SQLDYNAMIQUE) -- execute la requete sql

04/08/2006, 07h26
ZERS

Citation:

Envoyé par fdatdev06

Ok, alors j' ai une table Zone_Geo:
ZoneGeo
Id Zge_Num Zge_DIN Zge_DINNum
1 50 0
2 51 1 2222
3 52 1
4 53 1 2222
5 54 1 5555
6 55 1 7777
7 56 1 7777

Je veux avoir une table DIN:
DIN
Id DIN_Num Zge_Id Ordre
3 9999_50 1 1
4 2222_51 2 2
5 9999_52 3 3
6 2222_53 4 4
7 5555 5 5
8 7777_55 6 6
9 7777_56 7 7

La colonne ordre doit etre manipulable par logiciel et contenir plusieur fois la meme valeur.

Questions :
1- je suppose que ta table DIN est créée une seule fois. Quand la purges-tu ? Jamais ?

2- Pourquoi sa colonne ID doit être incrémentée de 1 ? Est-ce parceque tu la déclare comme clé unique ? Ou bien parce que tu veux l'afficher dans l'ordre ?
Parce que si c'est uniquement une raison de clef unique, y'a vachement plus simple et pratique : déclarer ta colonne comme UNIQUEIDENTIFIER

3- Dans ton exemple ci-dessus, à quoi sert la colonne 'ordre' ? Car si elle est modifiable par les utilisateurs, n'as-tu pas interet à mettre une valeur par défaut, qui n'a pas de sens?

4- Enfin, toujours dans ton exemple numérique, tu as traité le cas du ZGE_NUM = 50, alors que son ZGE_DIN = 0. Or dans ton code, tu ne prends pas les ZGE_DIN a 0. Où est l'erreur ? Dans le code, ou l'exemple numérique ?
07/08/2006, 09h33
fdatdev06

1: Oui, la table DIN est vréée une seule fois, on ne la purge jamais.
2: En effet, c est bien une clé unique dont la valeur doit commencer à une position donnée.
3: La colonne ordre peut etre modifiable par l utilisateur, mais pas necessairement. Elle indique l'ordre dans lequel les elements doivent apparaitre. L utilisateur peut eventuellement le changer.
4: Desole, l erreur est dans l exemple.