CREATE PROCEDURE spReturnJobState (@JobID uniqueidentifier, @JobState int
OUTPUT)
/****************************************************************************************
Description:      Script to return the job state of the data load job  so
if it is running we can stop it.
Returns:    (None)
Author:           Paul Ibison (xt 26163)
Date Created:     22/12/2004
Revisions:
*****************************************************************************************/
AS

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, 'dbo'

SELECT @JobState = job_state FROM #xp_results WHERE job_id = @JobID

DROP TABLE #xp_results

GO


declare @retstatus int
exec dba_admin..spReturnJobState 'E2682241-48BC-479B-BD99-795712292720',
@retstatus output
if @retstatus = 1 --executing
begin
      exec msdb..sp_stop_job 'UPLOAD_PROCESS_DATA_JOB'
end