Start, Stop, Manage MS SQL Server Agent Job using C#

You may also like...

2 Responses

  1. AP4200B says:

    Hello Mr. Kaarthik ,
    First of all thank you so much for your help. This is awesome.

    I am trying to run a job and show the status of the running job in the label1.txt of the win form.
    When this code executes, it looks like the job is running on the back ground as i can check in ssms that the job is executing.
    but the label information does not change. How can i do this .?
    I want to run the job but also at the same time, i want to know the status of the job that is running, .
    My job has 7 steps i want to show in lablel txt that, job is in progress and which step its at, and when it goes to next step i want the label text to update accordingly, or i can have it on a list box too , but i cannot do this for some reason, the label text never changes to anything,,,

    here is my code,,

    Any help is appreciated . Thank you , i will wait for your response,

    Thank you again..

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using Microsoft.SqlServer.Management.Smo;
    using Microsoft.SqlServer.Management.Sdk.Sfc;

    using Microsoft.SqlServer;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo.Agent;

    namespace run_a_job
    {
    public partial class Form1 : Form
    {
    public Form1()
    {
    InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
    string connectionstring = @”Data Source=XYZ;Initial Catalog=SSDB;User ID= XYZ;Password=XXX;Trusted_Connection=True;Integrated Security=SSPI;”;
    Server server = new Server(“XYZ”);
    SqlConnection DbConn = new SqlConnection(connectionstring);
    ServerConnection conn;
    Job job = server.JobServer.Jobs[“RUNIT”];

    var lastRunDate = job.LastRunDate;

    //i want to make sure that this job has not been already triggered
    if (job.CurrentRunStatus == JobExecutionStatus.Idle)

    job.Start();

    while (job.CurrentRunStatus == JobExecutionStatus.Executing)
    {
    job.Refresh();
    label1.Text = “Current status is ” + job.CurrentRunStatus.ToString() + Environment.NewLine + job.CurrentRunStep.ToString();
    System.Threading.Thread.Sleep(3000);
    //job.Refresh();
    }

    }

    }

    }

    • Kaarthik says:

      Hi, Thanks for reaching out to us here.

      To get the job status immediately after starting the job, you need to call job.Refresh();

      if job.Refresh() is not invoked, the object “job” will be holding the old values.

      In your code use the below sequence to start the job and get its current running status

      
      job.Start();
      job.Refresh();
      label.Text=job.CurrentRunStatus.ToString();
      

      One logical issue which I could see in the code is, you are using job.CurrentRunStatus == JobExecutionStatus.Executing as while loop termination condition.

      Updating the text box status happens inside this loop. When job execution is completed or it is in any other state, while loop condition will be evaluated to false, and your text box will always remain in status as Executing.

      Retrieving status of each step in a job
      We do not have direct method to retrieve this details. You have to come with a logic of your own.

      job.CurrentRunStep() returns the string in the pattern of 1 (step_name)

      Here number indicated the current step under execution followed by step name. With step number you can consider all steps before the current step number are completed and after the current step number are yet be be started

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: