Start, Stop, Manage MS SQL Server Agent Job using C#
We use MS SQL Server Agent used to execute SQL queries in a scheduled period. On some business scenarios, we might need to execute the SQL Server Agent jobs on demand or from another application. This article describes the different ways in which we can execute a SQL Server Agent job from a Dot. Net platform windows application or ASP. Net Core/MVC/Web API.
Methods to Start/Manage SQL Server Agent job using C#
1. Creating SQL Command and executing the in-build Stored Procedure msdb.dbo.sp_start_job
2. Start or Manage SQL Server Agent Jobs using Microsoft.SqlServer.SqlManagementObjects NuGet Package
Method 1: Starting SQL Server Agent Job with SQL Command Execution in C#
Starting an SQL Server Agent job using SQL Command is straightforward. The steps that we follow are
1. Open connection to server using SqlConnection
2. Create SqlCommand to the query msdb.dbo.sp_start_job
3. Set SqlCommand type to StoredProcedure
4. Add Parameter with value to the command as cmd.Parameters.AddWithValue("@job_name", "your_job_name");
5. Execute non query command
SqlConnection conn = new SqlConnection("Data Source=<your_db>;Integrated Security=True");
conn.Open();
SqlCommand cmd = new SqlCommand("msdb.dbo.sp_start_job",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@job_name", "<your_job_name>");
cmd.ExecuteNonQuery();
Method 2: Starting SQL Server Agent Job with Microsoft.SqlServer.SqlManagementObjects in C#
Using Microsoft.SqlServer.SqlManagementObjects library provides us, more control over the SQL Server Agent from within a C# application. We can perform almost all the operations related to SQL Server Agent like start/stop a job, get last run result and time, get current execution status and much more with simple one line code.
As an initial step, add NuGet Package reference Microsoft.SqlServer.SqlManagementObjects to your project.
Starting SQL Agent Job from C#
The below code uses the current logged in windows user credentials to connect with MS SQL Server.
“server.JobServer.Jobs” provides us the collection of all available jobs from the SQL Agent. We can use the job index or name to filter the required job.
Server server = new Server("<replace_with_your_server>");
server.JobServer.Jobs["<replace_with_your_job_name>"]?.Start();
Enabling/Disabling SQL Agent Job using C#
To disable the job set the property IsEnabled to false and call the Alter() function.
Server server = new Server("<replace_with_your_server>");
var job = server.JobServer.Jobs["<replace_with_your_job_name>"];
job.IsEnabled = false; //Disable the job
job.Alter(); //Refresh changes with server
Retrieve common SQL Agent Job details using C#
Server server = new Server("<replace_with_your_server>");
var job = server.JobServer.Jobs["<replace_with_your_job_name>"];
Console.WriteLine(job.CurrentRunStatus.ToString()); // Current running status
Console.WriteLine(job.CurrentRunStep);
Console.WriteLine(job.LastRunOutcome.ToString()); // Last run result
Console.WriteLine(job.LastRunDate.ToString());
Console.WriteLine(job.NextRunDate.ToString());
Console.WriteLine(job.OwnerLoginName);
Console.WriteLine(job.DateCreated.ToString());
Console.WriteLine(job.DateLastModified.ToString());
Managing Connection to SQL Server
Connections are managed automatically by the Microsoft.SqlServer.SqlManagementObjects library. If you wish be manage it by yourself the below logic has to be followed.
var srv = new Server("<your_server">);
srv.ConnectionContext.AutoDisconnectMode = AutoDisconnectMode.NoAutoDisconnect;
srv.ConnectionContext.Connect(); //Connect
Console.WriteLine(srv.Information.Version);
srv.ConnectionContext.Disconnect(); //Disconnect
Using SQL Authentication
In order to use SQL Authentication with SqlManagementObjects library, the Connetion Context property has to be updated with User Name and Password. Below is a code snippet.
Server server = new Server("<your_server>");
server.ConnectionContext.LoginSecure = false;
server.ConnectionContext.Login = "user_name";
server.ConnectionContext.Password = "password";
server.JobServer.Jobs["<your_job>"]?.Start();
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();
}
}
}
}
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
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