Verificare status job de sql

Configurare noua (How To)

Situatie

Mai jos aveti 2 functii care impreuna returneaza statusul unui job care este primit ca si parametru.

Prima functie primeste ca si parametru numele jobului si returneaza identificatorul unic al acelui job, care este folosit ca si parametru in cea de a doua functie pentru a afla status-ul jobului.

Solutie

— SELECT dbo.JobID (‘NumeJob’)

CREATE FUNCTION dbo.JobID (
@JobName char(250)
)
RETURNS uniqueidentifier AS

BEGIN

RETURN (
(SELECT job_id from msdb.dbo.sysjobs WHERE name = @JobName )
)
END

— SELECT dbo.JobStatus (‘IdentificatorUnicJob’)
CREATE FUNCTION dbo.JobStatus (
@JobID uniqueidentifier
)
RETURNS int AS

/*
— STATUS
0 – Not executing

1 – Current executing

2 – Waiting for thread

3 – Between retries

4 – Faileure during last run

5 – Suspended

6 – (not used)

7 – Performing completion actions

NULL – Job does not exists

*/

BEGIN

RETURN (
(SELECT TOP 1 ISNULL(last_executed_step_id, 0)
FROM msdb.dbo.sysjobactivity
WHERE job_id = @JobID
ORDER BY run_requested_date DESC )
)
END

Tip solutie

Permanent
Etichetare:

Voteaza

(11 din 22 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?