Job LastRunOutCome??

[Phonzo]

Hi all,

I am trying to determine the status of a Job after it has finished
running, but I don't get the correct results:

I first check if the Job is currently Running:

Private Function isJobExecuting(sJobName As String) As Boolean

Dim JobServer As String

'Execute a SQL Server Job here
'Initialise the SQL Server

Set oSQLServer = New SQLDMO.SQLServer

'Initialise the Login time out
oSQLServer.LoginTimeout = 10

'Disconnect any connections first
oSQLServer.Disconnect

'Connect to the Server
oSQLServer.Connect JobServer, CStr("sa"), CStr("")

'Set Job and start it
oSQLServer.JobServer.Jobs.Refresh

Set oJob = oSQLServer.JobServer.Jobs(sJobName)

'Before starting to run the Job the status must be checkes...
'And run the job only when its not currently running.
If (oSQLServer.JobServer.Jobs(sJobName).CurrentRunStatus =
SQLDMOJobExecution_Executing) Then
isJobExecuting = True
ElseIf (oSQLServer.JobServer.Jobs(sJobName).CurrentRunStatus =
SQLDMOJobExecution_PerformingCompletionActions) Then
isJobExecuting = False
End If

End Function

If Not I then Start the Job and check the status after that..

Everytime it tells me the status is 5...which means it cannot determine
the status.

While (isJobExecuting(JobName))
MsgBox ("RiskWatch Download Job busy executing. Please
wait...")
Wend

oJob.start

'After the Job has finished Check the Status...
While (isJobExecuting(JobName))
Wend

MsgBox oSQLServer.JobServer.Jobs(JobName).CurrentRunStatus


' Close the Job connection when done
Set oJob = Nothing

oSQLServer.JobServer.Jobs.Refresh

JobStatus = oSQLServer.JobServer.Jobs(JobName).LastRunOutcome

If JobStatus = 0 Then
MsgBox ("RiskWatch Download Job Failed ...")
RiskWatchDownload = False
Unload Me
ElseIf JobStatus = 1 Then
RiskWatchDownload = True
End If

I really will appreciate ur help!!

Regards,
Phonzo.

 

[TR]

I'm not sure how this would be accomplished in VB6 but I have done this in
VB.NET 2003 with the following.

Create a connection object to the MSDB database and tie out your data
command object (cmJOBStatus in the example below) to use this connection.
Your connection has to be made the MSDB database I believe.
for instance...

Dim drJSTAT As SqlClient.SqlDataReader

CONNMSDB.Open()

drJSTAT = cmJOBStatus.ExecuteReader()

With drJSTAT.Read
JOBStat = drJSTAT.Item("current_execution_status").ToString
End With

CONNMSDB.Close()

I'm assuming the MSDB database would be required in a VB6 call also. Hope
this helps...

Tim Roop

 

[Phonzo]

Thanks Tim,

I have another problem though,

I just want to know if there is a difference in stepping through the
code and actually running the application, coz according to my
knowledge there isn't.

When I step through the code I get correct results but wrong job status
results when executing the application.

Tnx.,

Phonzo.