Réupérer la date d'une exécution

**DEV-10** · 22/07/2009, 20h51

Bonjour

On a une procédure stockée qui roule à tous les soirs.

Dès fois, pour une raison ou une autre, l'exécution de cette procédure échoue.

J'aimerai rajouter une condition au code de cette procédure stockée pour qu'elle tourne en prenant compte de la date de la dernière bonne exécution + 1 jour.

Est-ce qu'il y a moyen d'extraire cette date du serveur et de l'implanter dans le corps de la procédure.

Merci.

**CinePhil** · 23/07/2009, 09h13

Ce genre de truc dépend probablement du SGBD. Comme celui-ci n'est pas précisé, contrairement aux règles du forum, il y a peu de chances que vous obteniez une réponse.

**DEV-10** · 23/07/2009, 14h13

Envoyé par CinePhil

Ce genre de truc dépend probablement du SGBD. Comme celui-ci n'est pas précisé, contrairement aux règles du forum, il y a peu de chances que vous obteniez une réponse.

C'est parce que je veux que a soit fait par des requêtes SQL dans le language SQL

Mon SGBD est SQL SERVER 2000.

Merci.

**elsuket** · 23/07/2009, 22h01

Bonjour,

Si vous exécutez toutes les nuits une procédure stockée, je suppose que vous commandez cette exécution par un job avec l'Agent SQL Server.
Dans ce cas, l'historique des exécutions de tous les jobs est requêtable dans la table système msdb.dbo.sysjobhistory.
La colonne run_status de cette table vous renseigne sur l'état d'exécution du travail :

- 0 = échec
- 1 = succès
- 2 = nouvelle tentative
- 3 = annulation
- 4 = en cours

Les colonnes run_date et run_time vous renseignent sur la date d'exécution du job. Comme ce sont des entiers, vous devez torturer un peu les valeurs pour en obtenir une date.

Cela ressemble donc à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE @NouvelleDateExec DATETIME
 
SELECT @NouvelleDateExec = DateTimeExec + 1
FROM
(
	SELECT MAX(CAST(STUFF(STUFF(DateExec, 12, 0, ':'), 15, 0, ':') AS DATETIME)) AS DateTimeExec
	FROM
	(
		SELECT CAST(H.run_date AS CHAR(8)) + ' ' +
				CASE LEN(CAST(H.run_time AS VARCHAR(6)))
					WHEN 5 THEN '0' + CAST(H.run_time AS CHAR(5))
					ELSE CAST(H.run_time AS CHAR(6))
				END AS DateExec
		FROM msdb.dbo.sysjobs AS J
		JOIN msdb.dbo.sysjobhistory AS H
			ON J.job_id = H.job_id
		WHERE J.name = 'TEST'
		AND H.run_status = 1
		AND H.step_id = 1
	) AS EXEC_JOB_TEST
) AS DER_EXEC_JOB_TEST_OK
 
SELECT @NouvelleDateExec

@++

**DEV-10** · 24/07/2009, 20h30

Envoyé par elsuket

Bonjour,

Si vous exécutez toutes les nuits une procédure stockée, je suppose que vous commandez cette exécution par un job avec l'Agent SQL Server.
Dans ce cas, l'historique des exécutions de tous les jobs est requêtable dans la table système msdb.dbo.sysjobhistory.
La colonne run_status de cette table vous renseigne sur l'état d'exécution du travail :

- 0 = échec
- 1 = succès
- 2 = nouvelle tentative
- 3 = annulation
- 4 = en cours

Les colonnes run_date et run_time vous renseignent sur la date d'exécution du job. Comme ce sont des entiers, vous devez torturer un peu les valeurs pour en obtenir une date.

Cela ressemble donc à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE @NouvelleDateExec DATETIME
 
SELECT @NouvelleDateExec = DateTimeExec + 1
FROM
(
    SELECT MAX(CAST(STUFF(STUFF(DateExec, 12, 0, ':'), 15, 0, ':') AS DATETIME)) AS DateTimeExec
    FROM
    (
        SELECT CAST(H.run_date AS CHAR(8)) + ' ' +
                CASE LEN(CAST(H.run_time AS VARCHAR(6)))
                    WHEN 5 THEN '0' + CAST(H.run_time AS CHAR(5))
                    ELSE CAST(H.run_time AS CHAR(6))
                END AS DateExec
        FROM msdb.dbo.sysjobs AS J
        JOIN msdb.dbo.sysjobhistory AS H
            ON J.job_id = H.job_id
        WHERE J.name = 'TEST'
        AND H.run_status = 1
        AND H.step_id = 1
    ) AS EXEC_JOB_TEST
) AS DER_EXEC_JOB_TEST_OK
 
SELECT @NouvelleDateExec

@++

Merci pour ces explications.

Lorsque j'exécute ce code tel quel ca me donne "Null"

Est-ce que 'TEST' reprèsente le nom de ma job ?

**elsuket** · 25/07/2009, 11h16

Est-ce que 'TEST' reprèsente le nom de ma job ?

Exact, désolé de ne pas te l'avoir précisé

@++

**DEV-10** · 27/07/2009, 19h15

Envoyé par elsuket

Exact, désolé de ne pas te l'avoir précisé

@++

Elsuket

Y a t-il une autre façon plus simple d'avoir ce que je veux.

Un genre de select qui va aller lire la date de la dernière bonne exécution d'une night job qui s'appelle TOTO ?

**elsuket** · 27/07/2009, 21h08

Oui, vous pouvez écrire une fonction pour cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE FUNCTION FnGetLastSuccessfulJobExecution
	(@nomJob SYSNAME)
	RETURNS DATETIME
AS
BEGIN
	RETURN
	(
		SELECT DateTimeExec + 1
		FROM
		(
			SELECT MAX(CAST(STUFF(STUFF(DateExec, 12, 0, ':'), 15, 0, ':') AS DATETIME)) AS DateTimeExec
			FROM
			(
				SELECT CAST(H.run_date AS CHAR(8)) + ' ' +
						CASE LEN(CAST(H.run_time AS VARCHAR(6)))
							WHEN 5 THEN '0' + CAST(H.run_time AS CHAR(5))
							ELSE CAST(H.run_time AS CHAR(6))
						END AS DateExec
				FROM msdb.dbo.sysjobs AS J
				JOIN msdb.dbo.sysjobhistory AS H
					ON J.job_id = H.job_id
				WHERE J.name = @nomJob
				AND H.run_status = 1
				AND H.step_id = 1
			) AS EXEC_JOB_TEST
		) AS DER_EXEC_JOB_TEST_OK
	)
END

Un exemple d'utilisation :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT dbo.FnGetLastSuccessfulJobExecution('TOTO')

@++

**DEV-10** · 27/07/2009, 22h03

Envoyé par elsuket

Oui, vous pouvez écrire une fonction pour cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE FUNCTION FnGetLastSuccessfulJobExecution
    (@nomJob SYSNAME)
    RETURNS DATETIME
AS
BEGIN
    RETURN
    (
        SELECT DateTimeExec + 1
        FROM
        (
            SELECT MAX(CAST(STUFF(STUFF(DateExec, 12, 0, ':'), 15, 0, ':') AS DATETIME)) AS DateTimeExec
            FROM
            (
                SELECT CAST(H.run_date AS CHAR(8)) + ' ' +
                        CASE LEN(CAST(H.run_time AS VARCHAR(6)))
                            WHEN 5 THEN '0' + CAST(H.run_time AS CHAR(5))
                            ELSE CAST(H.run_time AS CHAR(6))
                        END AS DateExec
                FROM msdb.dbo.sysjobs AS J
                JOIN msdb.dbo.sysjobhistory AS H
                    ON J.job_id = H.job_id
                WHERE J.name = @nomJob
                AND H.run_status = 1
                AND H.step_id = 1
            ) AS EXEC_JOB_TEST
        ) AS DER_EXEC_JOB_TEST_OK
    )
END

Un exemple d'utilisation :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT dbo.FnGetLastSuccessfulJobExecution('TOTO')

@++

Merci beaucoup !!

Est-ce que je dois faire des changements selon si je suis dans SQL Server 2000 ou SQL Server 2005 ?

**elsuket** · 28/07/2009, 11h10

Non, car ces tables existent aussi bien sous SQL Server 2000 que sous les versions ultérieures de SQL Server

@++

**MatthieuQ** · 03/08/2009, 12h09

Bonjour,
Je trouve ce code intéressant pour les "jobs", mais est-il envisageable de l'adapter aux procédures stockées ?
Je m'explique : j'aimerais pouvoir connaître la dernière date d'exécution d'une procédure stockée (nous en avons des dizaines et des dizaines qui s'accumulent depuis des années - je viens d'arriver dans la boite - et j'aimerais retrouver l'historique de chacune

).

En remplaçant les mots clefs de la fonction msdb.dbo.sysjobs & msdb.dbo.sysjobhistory par leurs équivalents pour les procédures stockées ? Lesquels sont-ils ? je ne sais pas...

Sous SQL Server 2005

Merci par avance de votre contribution...

**SQLpro** · 03/08/2009, 12h41

j'aimerais pouvoir connaître la dernière date d'exécution d'une procédure stockée

Impossible et innevisageable. imaginez le poids d'une telle scrutation sur un serveur fortement sollicité !

A +

**MatthieuQ** · 03/08/2009, 13h47

Merci de la réponse.
Peut-on envisager une autre solution ? Mon but étant d'isoler les procédures stockées utilisées de celles qui ne le sont plus.

**elsuket** · 03/08/2009, 23h46

Bonjour,

Vous trouverez les procédures stockées les fréquemment utilisées (et seulement celles-ci) référencées dans le cache de procédures.
Pour cela vous devez utiliser la vue de gestion dynamique sys.dm_exec_cached_plans et la fonction de gestion dynamique sys.dm_exec_sql_text

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT CP.desColonnes,
		SQL.mesColonnes
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text(CP.sql_handle)AS SQL

Vous pouvez aussi créer une trace à l'aide de l'outil SQL Server Profiler accessible par le menu outils de SQL Server Management Studio, en filtrant sur les événements "RPC:Starting" et "SP:Starting".

@++

**MatthieuQ** · 04/08/2009, 09h38

Bonjour,
Merci de votre réponse.

J'obtiens un message d'erreur à l'exécution du script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Msg 102, Level 15, State 1, Line 4
Syntaxe incorrecte vers '.'.

Faut-il installer une quelconque fonction supplémentaire ?

Par avance, merci de votre réponse.

**DEV-10** · 04/08/2009, 21h20

Envoyé par DEV-10

Merci beaucoup !!

Est-ce que je dois faire des changements selon si je suis dans SQL Server 2000 ou SQL Server 2005 ?

Bonjour Elsuket

Faut-il mettre cette fonction avec la section "Stored Procedures" ?

J'ai crée ma job sur SQL SERVER 2000 et dans l'onglet Step j'ai mis

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

exec 'ma_procedure_stockee'

dans le textbox Command mais il ne veut pas exécuter cette précédure stockée. manque t-il quelque chose entre exec et 'ma_procedure_stockee' ?

Merci.

**elsuket** · 04/08/2009, 21h48

Réponse à MatthieuQ

Il y a effectivement une erreur dans la requête que je vous ai proposé :
c'est plan_handle et non pas sql_handle que nous devons utiliser (la colonne sql_handle n'existe pas dans la vue sys.dm_exec_cached_plans)

En revanche je viens d'exécuter la requête suivante sous SQL Server 2005

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT CP.*,
		SQL.*
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle)AS SQL

Et je n'ai pas obtenu l'erreur que vous avez.
Vous avez probablement oublié une virgule entre deux spécifications de colonnes dans la requête lorsque vous avez remplacé "desColonnes" et "mesColonnes" dans la première requête que je vous ai proposé

Réponse à DEV-10

Faut-il mettre cette fonction avec la section "Stored Procedures" ?

Attention, une fonction n'est pas une procédure stockée.

Lorsque vous exécutez le lot CREATE FUNCTION, le module SQL est compilé comme une fonction, et vous n'avez pas le choix de la "placer" dans une catégorie

Vous pouvez vous inspirer du code de la fonction pour écrire une procédure stockée qui stockerait dans une table, par exemple, tous les matins, la dernière bonne exécution d'un job (c'est un exemple, cela n'a aucun intérêt puisqu'on peut facilement le retrouver dans les tables de MSDB

)

dans le textbox Command mais il ne veut pas exécuter cette précédure stockée. manque t-il quelque chose entre exec et 'ma_procedure_stockee' ?

Vous ne pouvez en effet pas appeler une procédure stockée de cette façon : vous devez écrire directement:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC dbo.ma_procedure_stockee

N'oubliez pas de préciser la base de données, ou écrivez :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC maBD.dbo.ma_procedure_stockée

En apparté, notez que vous pouvez construire votre requête en la stockant dans une variable de type VARCHAR, et exécuter celle-ci en écrivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC (@maRequete)

Vous remarquerez la présence des parenthèses

@++

**DEV-10** · 04/08/2009, 22h58

[quote=elsuket;4541098]Réponse à MatthieuQ

Réponse à DEV-10

Attention, une fonction n'est pas une procédure stockée.

Lorsque vous exécutez le lot CREATE FUNCTION, le module SQL est compilé comme une fonction, et vous n'avez pas le choix de la "placer" dans une catégorie

Vous pouvez vous inspirer du code de la fonction pour écrire une procédure stockée qui stockerait dans une table, par exemple, tous les matins, la dernière bonne exécution d'un job (c'est un exemple, cela n'a aucun intérêt puisqu'on peut facilement le retrouver dans les tables de MSDB

)

Qu'est ce que tu veux dire par "une catégorie" ?

Est-ce que je peux inclure cette fonction directement au début de ma procédure stockée et je l'appelle dans la procédure stockée elle même ?

Merci.

**elsuket** · 04/08/2009, 23h15

Par catégorie j'entendais "Procédures stockées", "Fonctions", ... comme le présente SQL Server Management Studio.

Est-ce que je peux inclure cette fonction directement au début de ma procédure stockée et je l'appelle dans la procédure stockée elle même ?

Vous pouvez effectivement appeler une fonction dans une procédure stockée (mais l'inverse est faux et c'est normal

)

@++

**MatthieuQ** · 05/08/2009, 09h32

réponse à elsuket

Merci, ça fonctionne. Et effectivement, une trace n'est pas malvenue.

Réupérer la date d'une exécution

Développement SQL Server

Discussions similaires

Partager

Partager