Recently, I had an issue where I needed to be able to trigger a SQL Server Job from a Console Application.
Now initially, I had the knee-jerk reaction of hitting the good ol' ADO.Net SQL Libraries, to invoke my overused little friend SqlCommand and his cousin sp_start_job. And to be honest, there is nothing wrong with this approach, and more often than not, that's all you'll need. However, for my particular situation I needed something a little more powerful, as the problem with invoking sp_start_job in this way, is that its done in a fire-and-forget manner. You see, sp_start_job DOES have a return value when its called, but that only indicates that the job has started successfully (or not), and includes nothing of what happens if the job itself fails. Given the client definitely needed to know what the heck was going on behind the scenes (at the very least from a high level perspective), I started in search of something to fulfil my requirement.
And so, on my journey, I discovered SQL Server Management Objects. And I'll be honest, I cried a little. In happiness. This little ripper of a library has everything you need to ping a SQL Server Agent and all of its Job related goodness in several very useful classes.
Side note: This library could probably be (ab)used in so many other ways, but for today I'll just focus on the SQL Agent side of things.
You need to add three references to your project:
- [your install location]\Microsoft SQL Server[your version]\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
- [your install location]\Microsoft SQL Server[your version]\SDK\Assemblies\Microsoft.SqlServer.Management.Sdk.Sfc.dll
- [your install location]\Microsoft SQL Server[your version]\SDK\Assemblies\Microsoft.SqlServer.Management.Smo.dll
- [your install location]\Microsoft SQL Server[your version]\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll
The first class I will focus on will be the Server class. This represents a connection to a given SQL Server, and its quite easy to use.
[code language="csharp"]
var sqlServer = new Server("(local)");
[/code]
Now, you can pass in a ServerConnection object (which has its own advantages), but for my purposes, passing in the server instance as a string was acceptable.
And that's it. The Server object is now ready to accept whatever you want to throw at it! Even better, the connection isn't currently open, so if we do;
[code language="csharp"] bool isOpen = sqlServer.ConnectionContext.IsOpen; [/code]
This will return false. So no need to worry about connections hanging around every time you spin up a new Server object.
Ok, so now we have a Server object, lets spin up the next object of our desire, the Job class. Now I'm going to cheat slightly by retrieving an existing Job on the Agent. This might seem obvious, but the SQL Server Agent process needs to be running if you ping the JobServer property on the Server class, otherwise the code will fail horribly. Not that I've encountered this... Nope, not me... Ok, I encountered this :(
[code language="csharp"]var job = server.JobServer.Jobs["MyAwesomeJob"];[/code]
And now we have a SQL Server Job in a nice, clean object, ready for us to play with! From here you can call the Start() method on the Job object, as well as iterate through the steps of the job. Honestly, the level of control you get over the SQL entities is nothing short of amazing, so I'd advise you to approach with caution.
The Refresh() method on the Job object sends a request to the server, asking for information about the SQL Server Job in context, and will update the objects properties to reflect any changes. However, there can be a delay between when the Start() method is called and the Job actually starting, so my current workaround for this is to use Thread.Sleep(1000) to give SQL Server time to process the Start() request. Once the SQL Server Agent kicks in, and Refresh() is called, the status of the object is updated to reflect the Job is executing (or that it has already failed). There is a better of way doing this than using Thread.Sleep, I'm sure, but for now it'll do. Hmm, I think I'll put this on my backlog to try and figure out a nicer way of doing this, and share the code when/if I've figured it out.
Now, its a simple while loop to ping the Job status every 1 second or so, and once we have the initial request change the status to JobExecutionStatus.Executing (this should take less than second), we check if its not currently Executing (side note: WriteLine is basically just a Console.WriteLine helper, that passes in a given colour).
[code language="csharp"]
while (true)
{
if (job.CurrentRunStatus != JobExecutionStatus.Executing)
{
if (job.LastRunOutcome == CompletionResult.Succeeded)
WriteLine("Hooray! Job Successful", ConsoleColor.Cyan);
else
WriteLine("Oh Noes! Job failed!", ConsoleColor.Red);
break;
}
job.Refresh();
WriteLine("Waiting...", ConsoleColor.Yellow);
Thread.Sleep(1000);
}
[/code]
Once this is done, we check the LastRunOutcome property, and act accordingly.
If you plan on using this, it might be a good idea to specify a timeout as this could potentially run forever if something unexpected happens on the SQL end.
Now, I'll address pulling out the error details from the Job, should the job fail.
Again, SQL Server Management Objects come into their own with this, as finding the error details is a case of looking at the DataTable that is returned from a failed job by calling EnumHistory() on the Job object, and filtering down on the DataTable to return the Message column for the correct row. Don't bother pulling out the error message from the first step (Step 0) itself, as this is more a high level "Job failed, was invoked by user X" type error, rather than anything useful. You may as well just output something like "An error has occurred, please contact Support.", as its around the same level of usefulness. Overall I think it depends on how informed your users need to be (or want them to be!). It's not the nicest way of going about things, but at this point it's just what SMO provides.
In the end, I decided on going with a couple of extension methods for this, as I wanted to keep the flow of the code as smooth (or at least what I think is smooth) as possible.
The first is the method to retrieve the last failed step of the job that failed.
[code language="csharp"]
public static JobStep GetLastFailedStep(this Job failedJob)
{
if (failedJob == null)
throw new ArgumentException("failedJob cannot be null");
if (failedJob.JobSteps == null || failedJob.JobSteps.Count == 0)
throw new ArgumentException("failedJob steps cannot be null or 0");
for (int i = (failedJob.JobSteps.Count-1); i >= 0; --i)
{
if (failedJob.JobSteps[i].LastRunOutcome == CompletionResult.Failed)
return failedJob.JobSteps[i];
}
return null;
}
[/code]
The second is the meat of the error reporting code, and retrieves the error message for the failed step,
[code language="csharp"]
public static string GetLastFailedMessageFromJob(this Job failedJob)
{
if (failedJob == null)
throw new ArgumentException("failedJob cannot be null");
JobStep failedStep = failedJob.GetLastFailedStep();
if (failedStep == null)
throw new ArgumentException("No failed step found for job " + failedJob.Name);
DataTable data = failedStep.EnumLogs();
JobHistoryFilter filter = new JobHistoryFilter()
{
JobID = failedJob.JobID,
OldestFirst = false,
OutcomeTypes = CompletionResult.Failed
};
var jobHistory = failedJob.EnumHistory(filter).Select("StepName='" + failedStep.Name + "'", "");
if (jobHistory != null)
return jobHistory[0]["Message"].ToString();
return string.Empty;
}
[/code]
So, this changes the snippet of code reporting job failure, meaning this;
[code language="csharp"]
WriteLine("Oh Noes! Job failed!", ConsoleColor.Red);
[/code]
Becomes this;
[code language="csharp"]
WriteLine("Oh Noes! Job failed!", ConsoleColor.Red);
WriteLine("Job failed with message: " + job.GetLastFailedMessageFromJob(), ConsoleColor.Red);
[/code]
And there you have it. Some (probably imperfect) code to help you run SQL Server Jobs, monitor them, and report any failures with a fair degree of accuracy. Obviously, this isn't a one size fits all sort of solution, but worked quite well for me.
I have also posted my complete solution on GitHub, located here, and welcome any feedback/improvements you can provide.
Good luck, and happy SQLing from C#!
Comments