473,322 Members | 1,409 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Job LastRunOutCome??

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.

Jan 20 '06 #1
2 2156
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" <al***********@treehousemis.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
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.

Jan 26 '06 #2
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.

Jan 31 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Resant | last post by:
To execute a job from VB, I use stored procedure with T-SQL : EXEC sp_start_job @job_name = 'DTSName' >From BOL, I know that procedure return 0 (success) or 1 (failure), but that's only to...
4
by: ikosianski | last post by:
Hallo, MSDB.dbo.BackupSet logs all backups is it possible find Status of Backup (Success / Failed)? Table "BackupSet" hasn't any information about it. I need log and notify all backups with...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.