Invoking a SQL Server job programmatically

Invoke a job from a certain step. Example:
EXECUTE msdb.dbo.sp_start_job @job_name='JobName', @step_name = 'Step3 Name'

Or do so programmatically on-demand:

SqlJobRunner.cs

using System;
using System.Timers;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;

namespace RefreshFactLedgerEntries
{
public class SqlJobRunner : IDisposable
{
public int TimeoutInterval { get; set; } = 60;
public event EventHandler JobCompleted;

static ServerConnection _conn;
static Server _server;

public SqlJobRunner(string sqlServer, string username, string password)
{
_conn = new ServerConnection(sqlServer, username, password);
_server = new Server(_conn);
}

public CompletionResult Run(string jobName)
{
Job job = GetJob(jobName);
return RunSynchronously(job);
}

private Job GetJob(string jobName)
{
Job job = _server.JobServer.Jobs[jobName]; //Get the specified job
return job;
}
private CompletionResult RunSynchronously(Job job)
{
/*
* Steps:
* (1) Start the job
* - If it's currently running, throw an error
* (2) Continually poll the job status
* (3) Once you get a job status of completed, return the result
* - If the job doesn't finish within the timeout, throw an error
*/

try
{
j
ob.Refresh();

// if not idle, throw an error
if (job.CurrentRunStatus != JobExecutionStatus.Idle)
{
throw new Exception(String.Format("SQL Agent Job {0} is currently busy (in state {1})!", job.Name, job.CurrentRunStatus.ToString()));
}

// start job
job.Start();

// set timer, specifically so we can timeout of long running jobs
Timer timer = new Timer(TimeoutInterval * 1000);
timer.Enabled = true;
timer.Elapsed += new ElapsedEventHandler(TimeoutExpired);

// Wait 10 seconds before polling for the status
System.Threading.Thread.Sleep(10 * 1000);
job.Refresh();

// poll job for status
while (job.CurrentRunStatus == JobExecutionStatus.Executing)
{
System.Threading.Thread.Sleep(10 * 1000); //wait 10 secs before checking again
job.Refresh();
}

// job finished, we don't need the timer anymore
timer.Enabled = false;
timer.Dispose();

return job.LastRunOutcome;
}
catch
{
throw;
}
finally
{
OnJobCompleted(new JobCompletedEventArgs(job.Name, true));
}
}

private void TimeoutExpired(object source, ElapsedEventArgs e)
{
throw new Exception(String.Format("The SQL Agent Job did not finish in the allotted time! Quitting process."));
}

public CompletionResult RunInSequence(string[] jobNames)
{
Job[] jobs = new Job[jobNames.Length];

// make sure these are in the same order as given
for (int i = 0; i < jobNames.Length; i++)
{
jobs[i] = GetJob(jobNames[i]);
}

return RunInSequence(jobs);
}

private CompletionResult RunInSequence(Job[] jobs)
{
foreach (Job job in jobs)
{
CompletionResult result = this.RunSynchronously(job);

if (result != CompletionResult.Succeeded)
{
throw new Exception(String.Format("SQL Agent Job {0} completed with a result of {1} !", job.Name, result.ToString()));
return CompletionResult.Failed;
}
}

return CompletionResult.Succeeded;
}

protected void OnJobCompleted(JobCompletedEventArgs e)
{
if (JobCompleted != null)
JobCompleted(this, e);
}

public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}

protected virtual void Dispose(bool disposing)
{
if (disposing)
{
// free managed resources
if (_server != null)
_server = null;

if (_conn != null)
{
_conn.Disconnect();
_conn = null;
}
}

// free native resources if there are any.
}
}
}


Program.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Net.Mail;
using System.Configuration;

namespace RefreshFactLedgerEntries
{
class Program
{
static string sqlServer = ConfigurationManager.AppSettings["SQLServer"];
static string userName = ConfigurationManager.AppSettings["UserName"];
static string password = ConfigurationManager.AppSettings["Password"];
static string emailRecipients = ConfigurationManager.AppSettings["EmailRecipients"];
static string smtp = ConfigurationManager.AppSettings["SMTPHost"];

static void Main(string[] args)
{
string[] jobs = new string[]
{
"ETL.SQL - Populate Data Warehouse 4 - Fact Ledger Entries"
};

try
{
Console.WriteLine(String.Format("Updating the data warehouse ({0}) with the latest Fact Ledger Entries.", sqlServer));
Console.WriteLine("*** Do not close this window until the process completes ***");
Console.WriteLine("*** This process may take up to an hour to complete ***");

using (SqlJobRunner runner = new SqlJobRunner(sqlServer, userName, password))
{
runner.JobCompleted += Runner_JobCompleted;
runner.RunInSequence(jobs);
}

SendEmail(
emailRecipients,
"Fact Ledger Entries Successfully Updated",
"The data warehouse has been updated with the latest Fact Ledger Entries.");

Console.WriteLine("The process has completed successfully. You may now close this window.");
}
catch (Exception e)
{
string errorInfo = String.Format(" Message: {0} \n Inner Exception: {1} \n Stack Trace: {2}",
e.Message,
e.InnerException,
e.StackTrace);

LogError(e);
SendEmail(
emailRecipients,
"Fact Ledger Entry Update Failed",
errorInfo
);

Console.WriteLine(String.Format("The Fact Ledger Entry update has failed. Please contact IT and include the following info: {0}", errorInfo));
}
finally
{
Console.ReadLine();
}
}

private static void Runner_JobCompleted(object sender, JobCompletedEventArgs e)
{
//Console.WriteLine(String.Format("SQL Agent Job {0} completed successfully", e.JobName));
}

static void SendEmail(string recipients, string subject, string body)
{
MailMessage mail = new MailMessage();
SmtpClient client = new SmtpClient(smtp, Convert.ToInt32(25));
client.UseDefaultCredentials = false;

foreach (var address in recipients.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries))
{
mail.To.Add(address);
}

mail.From = new MailAddress("donotreply@pdp.com");
mail.Subject = subject;
mail.Body = body;
client.Send(mail);
}

static void LogError(Exception e)
{
string dbErrorConn = String.Format("Data Source={0};Initial Catalog=ErrorLog;Persist Security Info=True;User Id={1};Password={2};",
sqlServer,
userName,
password);

using (SqlConnection conn = new SqlConnection(dbErrorConn))
{
using (SqlCommand cmd = new SqlCommand("splog_InsertError", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@appName", "RefreshFactLedgerEntry");
cmd.Parameters.AddWithValue("@Module", "RefreshFactLedgerEntry");
cmd.Parameters.AddWithValue("@Message", e.Message);
cmd.Parameters.AddWithValue("@StackTrace", e.StackTrace);
cmd.Parameters.AddWithValue("@InnerException", e.InnerException);
cmd.Parameters.AddWithValue("@Severity", 1);
cmd.Parameters.AddWithValue("@UserId", "System");

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
}
}