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
Leave A Comment?