Execute SQL SP and read results from vb, new at this

[intelwizrd]

I am writing a small little app that will let a member of our accounting department initiate a sql job to back up a database used by accounting so that she doesnt have to come find someone in technology everytime she wants a backup. here is what i have so far in regards to the job initiation phase:

Dim oSQLConn As SqlConnection = New SqlConnection()
Dim StrSQLJobExecute
Dim JobName

'Set Job Name based on Type selected
If JobType Is "temp" Then
    JobName = "TempGPBackup"
ElseIf JobType Is "me" Then
    JobName = "MEGPBackkup"
ElseIf JobType Is "ye" Then
    JobName = "YEGPBackup"
End If

StrSQLJobExecute = "sp_start_job @job_name = " & JobName

oSQLConn.ConnectionString = "Network Library=DBMSSOCN;" & _
    "Data Source=xxx.xxx.xxx.xxx,1433;" & _
    "Initial Catalog=msdb;" & _
    "User ID=" & Username & ";" & _
    "Password=" & Password

oSQLConn.Open()

Dim myCommandExecute As New SqlCommand(StrSQLJobExecute, oSQLConn)
myCommandExecute.ExecuteNonQuery()

and that works well. what i am trying to do now is to execute the stored procedure "sp_help_job" with parameters, then check the results returned to see if the job ran successfully.

the command i want to execute is "sp_help_job @job_aspect = JOB, @job_name = TempGPBackup"

and from that i want to store the values of the following fields into vars

last_run_date
last_run_time
last_run_outcome
current_execution_status

(all fields contain data of type int)

how would i do this? i keep reading different things and multiple ways but i dont know which one is correct. The SQL server is running SQL 2000 Ent if it matters.

----------------------------
Josh
CCNA, MCSE 2003(in progress)

 

[ArtieChoke]

Look into using the DMO library for SQL Server. It allows you to do anything you can do via EM (which is built on this library). It'll be much easier once you understand it than trying to get the desired results out of these sps.

"I think we're all Bozos on this bus!" - Firesign Theatre