DECLARE @jobname sysname ='Running Job' -- Enter the job name here
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM msdb..sysjobs Where Name = @jobname)
BEGIN
PRINT 'Job does not exists'
END
ELSE
BEGIN
CREATE TABLE #xp_results
(
job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL
)
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa'
IF EXISTS (
SELECT 1 FROM #xp_results X
INNER JOIN
msdb..sysjobs J ON X.job_id = J.job_id
WHERE x.running = 1 AND j.name = @jobname)
BEGIN
Print 'Job is Running'
END
ELSE
BEGIN
Print 'Job is not Running'
END
DROP TABLE #xp_results
END
Comments
Post a Comment