Getting one column from a sp_help_Job

Question

Getting one column from a sp_help_Job

lindss01

SSCrazy

Points: 2264
More actions
February 11, 2004 at 11:12 am

#161496

I'd like to renew this topic. I have not gotten an answer. Anybody out there got any suggestions?

I would like to access and use the column current_execution_status from the sp_help_job stored procedure. I'd like to say
DECLARE @CES AS int
Select @CES = current_execution_status FROM sp_help_job @job_name = '<jobname>', @job_aspect = 'JOB'

This does not seem to work.

Any suggestions

Steve

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

bp SSCarpal Tunnel Points: 4656 More actions · Answer 1

You are on the right track. You can load the results returned from the stored procedure into a temporary table and then retrieve it from there.

below is an example:

set nocount on

CREATE TABLE #loginfo (FileId int, FileSize int, StartOffset int, FSeqNo int, Status int, Parity smallint, Created varchar(20))

INSERT INTO #loginfo EXEC ('DBCC LOGINFO')

select * from #LOGINFO

set nocount off

HTH

Billy

lindss01 SSCrazy Points: 2264 More actions · Answer 2

OK I tried creating a table but sp_help_job must create a table wothin the procedure because I am getting "An INSERT EXEC statement cannot be nested".

So let me ask my question another way. I am trying to determine if a job started within a procedure is still running. How can I do that? I want to wait to make sure a job ends prior to going to the next step. Is the current execution status in a table in msdb somewhere?

Steve

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

You may create your own version of sp_help_job.

Do you want to be notified before going to next step? What do you try to achieve?

lindss01 SSCrazy Points: 2264 More actions · Answer 4

Here is what I am doing. I am creating a job that will backup the log of two databases (A and B) and do a complete backups. When these complete successfully I will get the latest backup file name from A and restore it as B and then backup B. I am using Maintenance Plan jobs to create the backups so that Backup set patterns are not disrupted. So when I start a job I need to know when it is complete so that I can move on to the next step of doing the restore.

Baggy SSChasing Mays Points: 649 More actions · Answer 5

Did you get a solution to this ? I am looking to do something similar re. Sart job, wait for job to complete before moving on in a proc.

Rick.

Baggy SSChasing Mays Points: 649 More actions · Answer 6

Further to this, I am currently executing the command which is found in step 1 of the job from the system tables. This then executes synchronously thus no need for a check and wait loop.

It would be nice to count the output from sp_job_help without having to build the table to collect the output. Not sure if this can be done though.

Chris Mackin-166947 SSChasing Mays Points: 653 More actions · Answer 7

You want to look into xp_sqlagent_enum_jobs as this is the proc that's nested into sp_help_job, so you can use this to write results directly to a temp table and then use.

Good Luck,

Chris Mackin