Problem getting execution status of a SQL Agent Job

I’m having trouble retrieving the execution status of a job – perhaps someone can help me.

I have a stored proc (on SQL 2005) that dynamically creates and executes a SQL Agent Job. The stored proc first has to check whether the job exists. If it already exists and is not active then I simply reuse the existing job. However if the job exists but is currently executing then I need to create a new job because there can be multiple instances of the program that the job runs. My problem is how to check whether the job is currently executing. I tried using sp_help_job as shown below but the rowcount seems to return 1 even when there are no jobs executing!

Exec msdb.dbo.sp_help_job

@.job_aspect = 'JOB',

@.execution_status = 0,

@.job_name = @.JobName

If @.@.rowcount > 0 Begin

…

End

I then tried this code…

If (select count(*) from msdb.dbo.sysjobs j

left outer join msdb.dbo.sysjobhistory h on j.job_id = h.job_id where name = @.JobName and

h.step_id = 1 and

run_status = 4) > 0

Begin

…

End

… but it doesn’t seem to pick up records even when the job is currently executing.

How can I, in T-SQL, get the execution status of the job?

I have the same issue. When I began to dig into sp_help_job, I noticed a call within the procedure to build a temp table:

exec master.dbo.xp_sqlagent_enum_jobs 1, {insert job owner here}

This procedure outputs a 'Running' field that will return a 1 if it the job is running, or otherwise a 0. This is not complete code for you, but should get you pointed in the right direction. I am going to build a temp table with this XP and filter on the job ID to return the status.

|||sp_help_jobactivity is the sp that should give status of jobs in SQL2005. See BOL for more info on this SP.sql

Problem creating folder through package

hi !

I am creating a directory folder inside the package. At first attempt while running the package it creates without any errors but when i run it second time it throws up warning message : Directory already exists. but the package succeeds.

When i try running it from another server through command line it fails. saying that Device it not Ready.

What is the proble here !!!!!!! Any help please

Jas

Guess you should be getting some error like the following - "Error: 0xC002F304 at File System Task, File System Task: An error occurred with the following error message: "The device is not ready."

Please check whether the drive and the path to the folder which you are trying to create exist in the Server.

Thanks,
Loonysan

|||

yes it does!!!!!

When i tried duplicating this problem from my machine accecssing a drive which was not there i got the same error SO i could understand that the drive and path should be correct to create the directory. When i tried the correct drive and path it work.

what are the methods to find what else the probelm could be? Permissions etc.

Thanks,

Jasmine