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).CurrentRunSta tus =
SQLDMOJobExecution_Executing) Then
isJobExecuting = True
ElseIf (oSQLServer.JobServer.Jobs(sJobName).CurrentRunSta tus =
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).CurrentRunStatu s
' 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.