Introduction
I would like introduce how to
use C# to manage SQL Server Agent Jobs.<o:p>
Lets try to check & wait for
the job status to be idle before we fire it:
Let the job be : Some_SqlAgentJob_J250
Sql Server : server112
Instance : Dev
Port : 20481
Using the code
Let's start a new Visual Studio Project(C#).
Add the following references:
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum
Microsoft.SqlServer.ConnectionInfo
Note: The references can be found in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ folder.
It means you need to have SQL Server Installed. I have used SQL Server 2008 R2. Ref:
using System;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;
using System.Timers;
namespace Kuthuparakkal.Util
{
class SQLJob
{
#region User Variables
const int timeoutInterval = 60;
static readonly string SqlServer = @"Server112\Dev,20481";
static readonly string SqlAgentJobName = "Some_SqlAgentJob_J250";
#endregion
static bool loopContinuity = false;
static Timer stateTimer;
static int CurrentRunRetryAttempt = 0;
static ServerConnection conn;
static Server server;
static Job job;
public static void Main(string[] args)
{
SetTimer();
try
{
conn = new ServerConnection(SqlServer);
server = new Server(conn);
job = server.JobServer.Jobs[SqlAgentJobName];
StartJob();
}
catch (Exception ex)
{
SetTimer(true);
Console.WriteLine("Failed to start the job :" + ex.Message);
throw ex;
}
finally
{
Destroyobjects();
}
}
static void Destroyobjects()
{
if (job != null)
job = null;
if (server != null)
server = null;
if (conn != null)
{
conn.Disconnect();
conn = null;
}
}
private static void SetTimer(bool cancel=false)
{
if (!cancel)
{
stateTimer = new Timer(timeoutInterval * 1000);
stateTimer.Enabled = true;
stateTimer.Elapsed += new ElapsedEventHandler(Tick);
}
else
{
if (stateTimer != null)
stateTimer.Dispose();
}
}
static public void Tick(object source, ElapsedEventArgs e)
{
loopContinuity = true;
Console.WriteLine(string.Format("Timeout reached at {0){1}CurrentRunRetryAttempt={2}" , e.SignalTime, Environment.NewLine, CurrentRunRetryAttempt));
throw new Exception(string.Format("Timeout reached at {0){1}CurrentRunRetryAttempt={2}", e.SignalTime, Environment.NewLine, CurrentRunRetryAttempt));
}
static void StartJob()
{
try
{
while (loopContinuity == false)
{
job.Refresh();
if (job.CurrentRunStatus == JobExecutionStatus.Executing)
{
CurrentRunRetryAttempt++;
loopContinuity = false;
System.Threading.Thread.Sleep(10 * 1000);
}
else
{
loopContinuity = true;
try
{
job.Start();
SetTimer(true);
}
catch
{
loopContinuity = false;
System.Threading.Thread.Sleep(10 * 1000);
}
}
string s = "CurrentRunStatus=" + job.CurrentRunStatus.ToString();
Console.WriteLine(s);
}
}
catch
{
throw;
}
}
}
}
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.