首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >在web应用程序中显示sql server代理作业的状态/输出

在web应用程序中显示sql server代理作业的状态/输出
EN

Stack Overflow用户
提问于 2014-01-23 19:37:10
回答 1查看 1.5K关注 0票数 0

是否可以显示sql server代理作业的状态/输出?

我有一个运行存储过程的应用程序,该存储过程实际上运行一个SQL Server代理作业(见下文),但是有没有办法显示正在运行的作业的状态?如果作业失败,我可以通过直接进入SQL Server Studio并查看作业的历史记录来检查,但无法从应用程序中判断。

理想情况下,我希望能够在应用程序中显示类似以下内容:

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

但如果有人知道如何展示

Job Ran SuccessfullyJob Failed. Error Message..

这是我当前运行存储过程的代码

感谢您的帮助,想法,建议,从哪里开始

 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
EN

回答 1

Stack Overflow用户

发布于 2018-07-30 08:34:26

这是我的答案。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>

..。和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

请注意,此解决方案是同步的:在SQL Server代理作业完成之前,回发不会返回。如果有人有更好的解决方案,请随时张贴。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/21307048

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档