Job LastRunOutCome??

20 Jan 2006 8:40 AM

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.

20 Jan 2006 9:29 AM

Razvan Socol

Hi, Phonzo

Your isJobExecuting function returns False when CurrentRunStatus =
SQLDMOJobExecution_PerformingCompletionActions, but also for any other
status other than SQLDMOJobExecution_Executing (because if you don't
assign any value to a boolean variable, it remains False). According to
Books Online, SQLDMOJobExecution_PerformingCompletionActions means "All
executable job steps complete. Job history logging in progress". Maybe
you should wait until CurrentRunStatus = SQLDMOJobExecution_Idle before
looking at LastRunOutcome.

Razvan

20 Jan 2006 1:56 PM

Phonzo

Hey Raz,

Thanks a lot 4 ur help, it worked....

Tnx a mil,

Cheers,
Phonzo.