Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Determining Job Success

Posted on 2008-10-15
11
643 Views
Last Modified: 2012-05-05
I want to run an agent job from a batch file, but I want the batch file to run until the job is finished.

Currently, I'm using sp_start_job, and that returns whether the job started.   - not what I'm lookin for.  I want to know if the job succeeded or failed.  In most cases I'd use sp_help_job but not sure how to do that with a batch file.

How can I achieve the desired result of having the batch file run until success or failure?

Thanks
b
0
Comment
Question by:bharvfor
  • 6
  • 5
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22723638
the problem is that sp_start_job starts the job asynchronous.  What you would have to do is write a while loop that checked for the most recent job outcome in sysjobhistory and stayed in the loop until the most recent step is step_id 0 (zero).  That will be the recorded step for the job outcome.
0
 

Author Comment

by:bharvfor
ID: 22723838
I need to do this within the context of a batch file.  Is there a proc that starts the job synchronously or one that returns success or failure?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22723996
no... are you using sqlcmd or osql to execute your sp_startjob now?
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22724027
Try this

sqlcmd -Sserver -E -Q" declare @jobid uniqueidentifier; select @jobid  = job_id from msdb.dbo.sysjobs where job_name = 'YourJobName'' exec sp_start_job @jobid; while (select top 1 step_id from msdb.dbo.sysjobhistory where job_id = @jobid order by istance_id)<>0 begin waitfor delay '0:0:10' /*checks every 10 seconds*/ end select top 1 run_status from msdb.dbo.sysjobhistory where job_id = @jobid and step_id=0 order by instanceid desc;" -n
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22724035
I think I got it all right... but you should copy from the -Q" to the ending " and paste it in a query window to test... that's the ACTUAL sql part
0
 

Author Comment

by:bharvfor
ID: 22725597
Thanks Brandon --
I think we're close but the SQL statement for the while loop never evaluates to anything other than 1.
I checked the job from beginning to end and it is always 1 so the loop just hangs.

I pasted the SQL - There were a few slight corrections

I'm not sure what to base the while on - It looks like the instance of the current job is not written to sysjobshistory table till the job has completed.
declare @jobid uniqueidentifier; 
select @jobid  = job_id from msdb.dbo.sysjobs where name = 'sfReplicateLeadsContactsAccounts' 
declare @jobid uniqueidentifier; 
select @jobid  = job_id from msdb.dbo.sysjobs where name = 'sfReplicateLeadsContactsAccounts' 
exec msdb..sp_start_job @job_id = @jobid
	while (select top 1 step_id from msdb.dbo.sysjobhistory where job_id = @jobid order by instance_id)<> 0  
		begin 
			waitfor delay '0:0:10' /*checks every 10 seconds*/ 
		end 
select top 1 run_status from msdb.dbo.sysjobhistory where job_id = @jobid and step_id = 0 order by instance_id desc

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
ID: 22726134
while (select top 1 step_id from msdb.dbo.sysjobhistory where job_id = @jobid order by instance_id)<> 0  

should be

while (select top 1 step_id from msdb.dbo.sysjobhistory where job_id = @jobid order by instance_id desc)<> 0
0
 

Author Comment

by:bharvfor
ID: 22730446
Brandon -

Now the statement doesn't enter the while loop because it evaluates to true right away.
Is there a way to get this to work using the sysjobhistory or would it be better to look at the sp_help_job stored proc.  Let me know the best approach in your opinion.

Thanks
b
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22730787
Try this change.  I put a 5 second delay in before it hits the while loop.  Because sp_Start_job is asynchronous, the job may not actually start before sp_start_job completes.  Another change may be necessary.

You may need to add another step to your job (that runs before your actual step).  Reason I am thinking is because the history record probably won't get written until the step completes.  So it may, as you are probably experiencing, have no history.  So if the below doesn't fix it, add a step that just does a simple statement like (print 'Job Starting').  without paren().  Make it the first step and the on success action be to go to your current first step.
declare @jobid uniqueidentifier; 
select @jobid  = job_id from msdb.dbo.sysjobs where name = 'sfReplicateLeadsContactsAccounts' 
exec msdb..sp_start_job @job_id = @jobid
waitfor delay '0:0:5'
        while (select top 1 step_id from msdb.dbo.sysjobhistory where job_id = @jobid order by instance_id desc)<> 0  
                begin 
                        waitfor delay '0:0:10' /*checks every 10 seconds*/ 
                end 
select top 1 run_status from msdb.dbo.sysjobhistory where job_id = @jobid and step_id = 0 order by instance_id desc

Open in new window

0
 

Author Comment

by:bharvfor
ID: 22733283
Good idea Brandon - The other idea I had is to put the max instance_id in a variable when I call sp_start_job and wait until the instance_id is greater than the variable.  I think that would work as well.
0
 

Author Comment

by:bharvfor
ID: 22733726
Perfect!  With the 5 second wait prior to the while loop and the addition of a 'dummy' step it works like a champ.  For those interested the option to use the max instance_id in a variable works as well.

Thanks again Brandon!

b
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Virtual SQL Server 2014 Standard 35 87
SQL Activity Monitor detail 2 23
SQLCMD Syntax 2 13
SQL Recursion schedule 13 13
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question