Show status/output of sql server agent job in web app

Question

is there any way to show the status/output of an sql server agent job?

I have an application that runs a stored procedure which actually runs an SQL Server Agent Job (See Below) but is there any way to show the status of that job that is running? If the Job fails I can check by going directly in to SQL Server Studio and viewing the history of the job but there is no way to tell from the application.

Ideally I'd like to be able to display something like this in the application:

Executing job...
Starting Step 1... Success
Starting Step 2... Success

But if any one knows how to even display

Job Ran Successfully or Job Failed. Error Message..

This is my current code that runs the stored procedure

thanks for any help, ideas, suggestions on where to start

 Sub btnImport_click(ByVal sender As Object, ByVal e As EventArgs)

    Dim sqlConnection1 As New SqlConnection("CONNECTION STRING")
    Dim cmd As New SqlCommand
    Dim rowsAffected As Integer
    cmd.CommandText = "dbo.runImportDGDataJob"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Connection = sqlConnection1
    sqlConnection1.Open()
    rowsAffected = cmd.ExecuteNonQuery()
    sqlConnection1.Close()
End Sub

Answer 1

Here is my answer. Default.aspx:

<%@ Page Title="Home Page" Language="VB" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.vb" Inherits="LaunchJobAndWaitTillDone_VB._Default" %>

<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">

    <div class="jumbotron">
        <h1>Launch Job And Wait Till Done (VB)</h1>
        <p class="lead">Launch SQL Server Agent Job, wait for it to finish, then display the result (VB.NET version)</p>
    </div>

    <div class="row">
        <div class="col-md-4">
            <asp:Button ID="btnImport" runat="server" Text="Import" />
        </div>
        <div class="col-md-4">
            <asp:Label ID="lblOutput" runat="server" Text=""></asp:Label>
        </div>
    </div>

</asp:Content>

... and Default.aspx.vb:

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class _Default
    Inherits Page

    Const ConnectionString As String = "Data Source=YOURSERVERNAMEHERE;Initial Catalog=msdb;Integrated Security=SSPI"
    Const JobName As String = "YOURJOBNAMEHERE"
    Shared ReadOnly WaitFor As TimeSpan = TimeSpan.FromSeconds(1.0)

    Enum JobExecutionResult
        Succeeded
        FailedToStart
        FailedAfterStart
        Unknown
    End Enum

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

    End Sub

    Private Sub btnImport_Click(sender As Object, e As EventArgs) Handles btnImport.Click
        Dim JobResult As JobExecutionResult = RunJob(JobName)

        Select Case JobResult
            Case JobExecutionResult.Succeeded
                lblOutput.Text = $"SQL Server Agent job, '{JobName}', ran successfully to completion."
            Case JobExecutionResult.FailedToStart
                lblOutput.Text = $"SQL Server Agent job, '{JobName}', failed to start."
            Case JobExecutionResult.FailedAfterStart
                lblOutput.Text = $"SQL Server Agent job, '{JobName}', started successfully, but encountered an error."
            Case Else
                lblOutput.Text = $"Unknown result from attempting to run SQL Server Agent job, '{JobName}'."
        End Select
    End Sub

    Function RunJob(ByVal JobName As String) As JobExecutionResult
        Dim JobResult As Integer

        Using JobConnection As SqlConnection = New SqlConnection(ConnectionString)
            Dim JobCommand As SqlCommand
            Dim JobReturnValue As SqlParameter
            Dim JobParameter As SqlParameter

            JobCommand = New SqlCommand("sp_start_job", JobConnection)
            JobCommand.CommandType = CommandType.StoredProcedure

            JobReturnValue = New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
            JobReturnValue.Direction = ParameterDirection.ReturnValue
            JobCommand.Parameters.Add(JobReturnValue)

            JobParameter = New SqlParameter("@job_name", SqlDbType.VarChar)
            JobParameter.Direction = ParameterDirection.Input
            JobCommand.Parameters.Add(JobParameter)
            JobParameter.Value = JobName

            JobConnection.Open()
            Try
                JobCommand.ExecuteNonQuery()
                JobResult = CInt(JobCommand.Parameters("@RETURN_VALUE").Value)
            Catch ex As SqlException
                JobResult = -1
            End Try
        End Using

        If JobResult <> 0 Then
            Return JobExecutionResult.FailedToStart
        End If

        While True
            Using JobConnection2 As SqlConnection = New SqlConnection(ConnectionString)
                Dim JobCommand2 As SqlCommand = New SqlCommand("sp_help_jobactivity", JobConnection2)
                JobCommand2.CommandType = CommandType.StoredProcedure

                Dim JobReturnValue2 As SqlParameter = New SqlParameter("@RETURN_VALUE", SqlDbType.Int)
                JobReturnValue2.Direction = ParameterDirection.ReturnValue
                JobCommand2.Parameters.Add(JobReturnValue2)

                Dim JobParameter2 As SqlParameter = New SqlParameter("@job_name", SqlDbType.VarChar)
                JobParameter2.Direction = ParameterDirection.Input
                JobCommand2.Parameters.Add(JobParameter2)
                JobParameter2.Value = JobName

                JobConnection2.Open()
                Dim rdr As SqlDataReader = JobCommand2.ExecuteReader()
                While rdr.Read()
                    Dim Msg As Object = rdr("message")
                    Dim Run_Status As Object = rdr("run_status")
                    If Not IsDBNull(Msg) Then
                        Dim Message As String = TryCast(Msg, String)
                        Dim RunStatus As Integer? = Run_Status
                        If Message IsNot Nothing AndAlso Message.StartsWith("The job succeeded") _
                            AndAlso RunStatus.HasValue AndAlso RunStatus.Value = 1 Then
                            Return JobExecutionResult.Succeeded
                        ElseIf Message IsNot Nothing AndAlso Message.StartsWith("The job failed") Then
                            Return JobExecutionResult.FailedAfterStart
                        ElseIf RunStatus.HasValue AndAlso RunStatus.Value = 1 Then
                            Return JobExecutionResult.Unknown
                        End If
                    End If
                End While
            End Using

            System.Threading.Thread.Sleep(WaitFor)
        End While
    End Function
End Class

Note that this solution is synchronous: the postback won't return until the SQL Server Agent job finishes. If someone has a better solution, please feel free to post it.