[Server SQL 2000] Erreur sur une "stored procedure" [Résolu]

[hunteshiva]

Bonjour,
j'ai une erreur sur un de mes scripts et je ne comprend pas pourquoi...
*ce script fonctionne trés bien sur une autre machine, avec une base qui à la même structure mais pas le même contenu*

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
CREATE PROCEDURE SP_REC_PURGE
@p_nb_jour int 
AS
 
  -- Declaration  and initialisation de la variable de sauvegarde de @@Error.
  DECLARE @ErrorSave INT
  SET @ErrorSave = 0
 
  -- Déclaration du status du curseur
  DECLARE @Statuscursor INT
  SET @Statuscursor = 0
 
 
 
  DECLARE @li_nbre_of AS Int
  -- Déclaration des variables 
  DECLARE @Id_Rec			AS varchar(10)
  DECLARE @Vers_Rec			AS int
  -- Déclaration du curseur de la recherche des recettes
  DECLARE search_rec CURSOR LOCAL FOR
  SELECT ID_REC,
         VERS_REC
    FROM rec
   WHERE ETAT_REC	= 2
     AND DATE_MODIF	<= getdate() - @p_nb_jour
 
  -- Recherche des recettes
  Open search_rec
  Fetch next FROM search_rec INTO @Id_Rec,@Vers_Rec
   -- Tant que la liste des recettes n'est pas terminée 
  While @@Fetch_Status = 0
  Begin
  	SET SET @li_nbre_of  = 0
  	Begin transaction
        SET @li_nbre_of  =  (SELECT count(*) 
                               FROM ord_fab 
                              WHERE id_rec = @Id_Rec
                                AND vers_rec = @Vers_Rec)
        IF (@li_nbre_of = 0)
	Begin
          DELETE rec WHERE id_rec = @Id_Rec
                       AND vers_rec = @Vers_Rec
          SET @ErrorSave = @@Error
          IF (@ErrorSave = 0) 
          Begin
            Commit transaction
          End
          Else
          Begin
            Rollback transaction          
          End 
        End
    -- Positionnement sur l'enregistrement suivant
    Fetch next FROM search_rec INTO @Id_Rec,@Vers_Rec
  End 
  Close search_rec 
  Deallocate search_rec 
 
GO

mon job qui permet d'éxecuter cette procedure,
est donc en croix avec le message suivant

The job failed. The Job was invoked by User ****. The las step to run was step 1 (PURGE)

si je demande des détails

Executed as user: AUTORITE NT\SYSTEM.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION satetement is missing.
Previous count = 0, current count = 9.
[SQLSTATE 25000](Error 266)
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 0, current count = 9.
[SQLSTATE 25000](Error 266)
The step failed.

si je rajoute PRINT @@TRANCOUNT dans mon script

Executed as user: AUTORITE NT\SYSTEM.
1 [SQLSTATE 01000](Message 0)
2 [SQLSTATE 01000](Message 0)
3 [SQLSTATE 01000](Message 0)
4 [SQLSTATE 01000](Message 0)
5 [SQLSTATE 01000](Message 0)
6 [SQLSTATE 01000](Message 0)
7 [SQLSTATE 01000](Message 0)
8 [SQLSTATE 01000](Message 0)
9 [SQLSTATE 01000](Message 0)
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION satetement is missing.
Previous count = 0, current count = 9.
[SQLSTATE 25000](Error 266)
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 0, current count = 9.
[SQLSTATE 25000](Error 266)
The step failed.

rien de nouverau,
il me dit juste qu'il compte 9 fois mais je comprend pas ce qui le dérange avec le Previous count = 0, current count = 9

[iberserk]

A quoi servent vos commit et rollback dans votre cas?
Vous initialisez une transaction dans chacun de vos passage dans le curseur... que vous rollbackez si votre delete déclenche une erreur... or c'est la seule instruction de votre transaction?

[iberserk]

Dans le cas ou

IF (@li_nbre_of = 0)

Vous ne rollbackez pas la transaction ouverte...

vous êtes surement dans le cas ou dans chaque boulce @li_nbre_of=0

[SQLpro]

Il faut éviter les imbrications de transaction.. ou bien savoir les gérer. En effet les transactions imbriquées, cela n'existe pas. Il n'y a jamais qu'une seule transaction du fait de la notion d'atomicité des transactions.
Ce qui fait que si vous envoyez 35 BEGIN TRANSCATION, le premier ROLLBACK annule la transaction. En revanche quand vous envoyez un COMMIT cela décrémente le compteur de transaction de 1 et s'il passe à zéro, la transaction est réellement validée.
Pour tester ou vous en êtes dans votre empilement, vous pouvez utiliser la variable globale de session @@TRANCOUNT.

Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...ns-imbriquees/

A +

[iberserk]

De plus votre curseur est complètement inutile, la suppression peux à coups sûr se faire en une seule requête...

[hunteshiva]

A vrai dire, je n'ai pas d'énorme connaissance en Script
c'est une usine à gaz que j'ai détérée derriére un probléme...

l'objectif est de purgé les bases, en ne gardant que 2ans d'archives. *on joue sur les dates*

je ne comrpend pas ce que sont les commit transaction et rollback transaction , ni à qou ils servent

Par contre je vien de me rentre compte que mes tables ne sont pas identiques
celle ou je plante, a en faite une colonne de plus... mais je ne voi pas en quoi ça pose un probléme

[iberserk]

Remplacez votre code par cela:

Code :

1
2
3
4
5
6
7
8
9
10
11
CREATE PROCEDURE SP_REC_PURGE
@p_nb_jour int 
AS
 BEGIN
 DELETE 
    FROM rec
   WHERE ETAT_REC	= 2
     AND DATE_MODIF	<= getdate() - @p_nb_jour
     AND NOT EXISTS(SELECT * FROM ord_fab O
			WHERE O.id_rec=rec.ID_REC AND O.vers_rec=rec.VERS_REC)
END

[iberserk]

celle ou je plante, a en faite une colonne de plus... mais je ne voi pas en quoi ça pose un probléme

Ce n'est pas l'origine de votre problème

[hunteshiva]

OK,
j'essaye le code que vous m'avez donné. je vous tien au courant *rapidement*

[iberserk]

Une petite erreur s'est introduite dans mon script :-) je l'ai modifié

[hunteshiva]

dans ce code

Code :

1
2
3
4
5
6
7
8
9
10
11
CREATE PROCEDURE SP_REC_PURGE
@p_nb_jour int 
AS
 BEGIN
 DELETE 
    FROM rec
   WHERE R.ETAT_REC	= 2
     AND R.DATE_MODIF	<= getdate() - @p_nb_jour
     AND NOT EXISTS(SELECT * FROM ord_fab O
			WHERE O.id_rec=rec.ID_REC AND O.vers_rec=rec.VERS_REC)
END

il ne prend pas le code....
il me génére une erreur lorsque je check la syntax

Error 107:
The column prefix 'R' does not match with a table name or alias name used in the query.

PS: ton premier code fonctionné au niveau syntax mais toujours une erreur avec mon job ..

[iberserk]

Code :

1
2
3
4
5
6
7
8
9
10
11
CREATE PROCEDURE SP_REC_PURGE
@p_nb_jour int 
AS
 BEGIN
 DELETE 
    FROM rec
   WHERE ETAT_REC	= 2
     AND DATE_MODIF	<= getdate() - @p_nb_jour
     AND NOT EXISTS(SELECT * FROM ord_fab O
			WHERE O.id_rec=rec.ID_REC AND O.vers_rec=rec.VERS_REC)
END

Retirez simplement les deux "R." de la clause where

[iberserk]

PS: ton premier code fonctionné au niveau syntax mais toujours une erreur avec mon job ..

Heureusement... grâce aux contraintes...

[hunteshiva]

ok c'est bon le scritp est correct sytaxiquement et le job tourne
mais quelles sont les différences par rapport a mon code d'origine?...

je voudrai justifier ce travaille, bien que je comprenne les requétes SQL faite.

[iberserk]

Ok...
L'ancien code commençait par lister les lignes à supprimer.
Puis via un CURSOR (à éviter autant que faire se peut...) pour chacune de ces lignes il vérifiait que la ligne n'était pas "utilisée" par une autre table (ord_fab)
Si ce n'était pas le cas la ligne était supprimée...

Du coups si tu as 1000 ligne à supprimer... ton ancien code faisait 2000 requetes...

SQL permet de faire tout celà en une seule fois car c'est un langage ensembliste...

Mon nouveau code peux se traduire comme celà:

"Supprime toutes les lignes vieille de plus de N jours et dont il n'existe pas de lien avec la table ord_fab"...

[hunteshiva]

juste une chose que je n'ai pas compris dans le script

quand tu fait

Code :

SELECT * FROM ord_fab O WHERE O.id_rec=rec.ID_REC AND O.vers_rec=rec.VERS_REC

pourquoi tu fait O.id_rec?
le "FROM ord_fab O" donne tout la structure à O de la table ord_fab?

[iberserk]

Le O est juste un alias cela t'evite juste de devoir retaper le nom de ta table en entier à chaque fois...

Sinon j'aurais du taper çà:

Code :

SELECT * FROM ord_fab WHERE ord_fab.id_rec=rec.ID_REC AND ord_fab.vers_rec=rec.VERS_REC

[hunteshiva]

Merci beaucoup "iberserk"
*vraiment merci, ça fonctionne nikel*