Tag Archive | c#

SQL Server Management Objects – An Update

So, after previous post called “Adventures with SQL Server Management Objects“, I decided to take a step back and see if I could improve my code. I wasn’t happy with the while(true) loop, as if the developer stars align in a certain way this could potentially mean an infinite loop.

This is Bad™.

Originally I had the idea of using Tasks (from the Task Parallelism Library) to monitor the status of switching from Idle to Executing and then Executing to anything else (signifying failure), and so I plugged away at my code briefly, while also coming to grips with the TPL. However, as time went on, I realised this code was getting messier and messier because a) I didn’t really know what I was doing, and b) as I got more familiar with Tasks and when/where to use them I realised I technically didn’t need them (as it being a console app, I didn’t really care about blocking the main thread). Not only that, but monitoring the status changes of the Job in this way was fraught with danger because sometimes (yay consistency) the time between the initial job.Start() and job.Refresh(), the job can execute and fail, making the status monitoring a bit flaky at best. So I threw away my changeset and started again (from my previous commit), this time without any usage of Tasks.

This is what I ended up with, edited for brevity;

    /* snip */
    var lastRunDate = job.LastRunDate;


    // The job.LastRunDate will only change once the job finishes executing, providing a way of monitoring the job
    while (job.LastRunDate == lastRunDate)

    if (job.LastRunOutcome == CompletionResult.Succeeded)
        WriteInfo("Hooray! Job Successful");
        WriteError("Oh Noes! Job failed!");
        WriteError("Job failed with message: " + job.GetLastFailedMessageFromJob());
    /* snip */

By monitoring when the LastRunDate property of the Job object changed, I was able to accurately determine when the job had actually stopped executing from the current attempt. Thankfully, the LastRunDate property only gets changed when the Job finishes executing (whether it fails or succeeds).

If you want to look at the full changeset, the details are here.

Technically though, this still blocks the main thread. I realised after I finished this, that you could wrap that entire chunk of code above inside a Task, using –

Task.Factory.StartNew( () => {

/* monitoring code here */


Unfortunately, this doesn’t have any of the CancellationToken stuff implemented in it. And honestly, I’m not entirely sure where it would go, or if its even necessary. That’s for another post though 🙂

Thanks for reading folks, have a great night/day.


Adventures with SQL Server Management Objects

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.

var sqlServer = new Server("(local)");

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;

 bool isOpen = sqlServer.ConnectionContext.IsOpen; 

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 😦

var job = server.JobServer.Jobs["MyAwesomeJob"];

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).

while (true)

    if (job.CurrentRunStatus != JobExecutionStatus.Executing)
        if (job.LastRunOutcome == CompletionResult.Succeeded)
            WriteLine("Hooray! Job Successful", ConsoleColor.Cyan);
            WriteLine("Oh Noes! Job failed!", ConsoleColor.Red);

    WriteLine("Waiting...", ConsoleColor.Yellow);

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.

        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;

The second is the meat of the error reporting code, and retrieves the error message for the failed step,

        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;

So, this changes the snippet of code reporting job failure, meaning this;

WriteLine("Oh Noes! Job failed!", ConsoleColor.Red);

Becomes this;

 WriteLine("Oh Noes! Job failed!", ConsoleColor.Red);
 WriteLine("Job failed with message: " + job.GetLastFailedMessageFromJob(), ConsoleColor.Red);

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#!

Challenge Accepted

Thought I would share an interesting problem I recently had at work.

Our client has a collection of InfoPath forms it uses to connect to web services for testing BizTalk interfaces from external systems. On the whole, these are pretty stock standard affairs, the relevant BizTalk orchestrations are exposed as Web Services (through the magic of BTSWebSvcPub) and the InfoPath forms (prettied up for client usage of course) submit test messages. This enables them to do pseudo end-to-end testing without having to get half the world involved. Amusingly (amusing because it only just occurred to us), a few days ago I realised we would need to upgrade these forms in line with our BizTalk 2010 upgrade project.

Let the games begin.

As far as things go, it was pretty straight forward. I refreshed the web service references and checked the forms still compiled. So far so good (well, minus a few bits and pieces cropping up, but not relevant to this post). And then came the testing of the forms, and by that I really mean… *head-desk*.

When I clicked the “Submit” button… nothing. Absolutely nothing. Oh Infopath was happily assuring me everything was ok and working awesomely, but on checking BizTalk (and the DB of the relevant application), absolutely nothing. No messages were suspending (BizTalk side), nor were there any validation errors (application side). So a bit of head scratching, a bit of googling and this left me with a lot of issues completely not relevant to my problem. On a side note, googling “vanishing web service requests” unsurprisingly doesn’t yield too much useful information. I also scoured the event viewers of the BizTalk servers and application servers (I even got desperate and checked the event viewer of the machine I was calling the Infopath forms from), all to no avail.

Then I checked the IIS logs of the machine where the web services were hosted, and I spotted a HTTP code Id never really seen; the “202” code. This is the “Request Accepted” HTTP code and means that the server has accepted the request (Yay), and the request (this is important) might or might not be acted upon, as it might be disallowed when processing actually takes place. Initally I thought “Awesome, so its definitely hitting IIS and getting through the web service, so WHY OH WHY isnt it getting through to BizTalk?”. So some more headscratching, googling and *head-desking* later, the penny finally dropped.

Accepted != Ok.

The HTTP codes 202 and 200 are completely different (duh), meaning that while it DID hit the web service successfully, the fact nothing happened in BizTalk afterwards means something must be stopping it in between when it hits IIS and when its supposed to hit BizTalk/MessageBox (keep in mind BizTalk is just a bunch of SQL DBs that require the correct credentials to connect to). This was why I wasnt getting any suspensions in BizTalk, it denying the credentials supplied by the web service as they didnt exist in the DB! So on checking the service accounts that these orchestration web services were using, it stuck out like a sore thumb… I had configured the web services to connect to outside entities using our normal dev service account. While this is ok for our local dev environments (as its what we mostly configure our web services with), I had forgotten that these web services were a completely different beast, and as such should have been configured with the default service account the rest of the IIS Application Pools were using!!

So once Id reconfigured the application pools and fired off an Infopath message, they hit BizTalk (and then the application) successfully! Huzzah!

While this is a bit of a *facepalm* moment for me, I thought it was interesting that in terms of actual ‘error messages’ there were none immediately visible, only the symptom of a vanishing message. It made me look a bit deeper into things than I usually would (in this world of “GOOGLE ALL THE ERROR MESSAGES!!1!1”), and was quite satisfying when I did eventually figure out what was going on. Thinking about it now actually, I think if I checked the DB Server event viewers Id probably find a few Audit Failure events. Hope this helps someone out there with a symptom of (seemingly) vanishing messages 🙂

Night all!


Like most things in life, hardly anything actually ever goes to plan. I did intend on writing a more in-depth entry on the new async features in C# 5 during the weekend, but had an insane weekend filled with relatives visiting/being visited. It wasnt until monday afternoon that I actually had a chance to sit down and do a bit of tinkering in my VM and give this stuff a shot by myself.

And thats when the problems started.

You know, I find new technologies to be a lot like the dialects of a particular (spoken) language. Spanish, for instance, is spoken by many, many countries around the world, but each country with their own particular dialect. Each of these countries does has an understanding of what the other country is saying, but the things differ slightly, usually in grammar or simply completely different words for the same thing. But they usually get the gist of whats being said.

This is how I felt last thursday at the Perth .Net Usergroup. While I was able to follow what Joe Albahari was saying and understanding the concepts behind it, when it came to me actually trying to communicate with my computer in the async ‘dialect’, lets just say there was very bloody brick wall by the end of the night. And I had a huge headache.

For my very first foray into the world of asynchronous programming (as Ive never really tinkered with the .Net 2+ Threading stuff, not seriously) was a form with two controls, a text box and a button. And all that was (supposed) to happen was on the click of the Button, “A” would appear in the textbox, courtesy of an awaited method. Thats it. Seriously.

This was my code…

private async void btnStart_Click(object sender, RoutedEventArgs e)

Task<string> x = await TaskEx.Run(() => GetA());
txtResults.Text = x.Result;


private async Task<string> GetA()

return await new Task<string>(() => “A”);


Those familiar (because I was not) with the Task classes introduced in .Net 4.0 will realise, as this kind gentleman pointed out for me, that I did not TaskEx.Run my new task. All I did was return it to the UI, and the x.Result locked out my UI thread (oh the irony!). Thank God for resources like MSDN and StackOverflow where MS employees will browse the forums and answer any number of completely inane questions. God (or whoever) bless you all!

When I changed my code in the GetA() method to:

return TaskEx.Run(() => “A”);

It worked! Huzzah! I had the letter A printed in my fancy WPF text box.

But it just goes to show how the different dialects of technology can trip you up in the most simple ways. I think my problem is I came into this without knowing anything but the very basics of Tasks and how they work, otherwise I would have realised in order to utilize a Task you must run it. Haha, sometimes you just gotta laugh 🙂

In fact, looking at that code now, I just realised I can refactor it a bit (the first cut of code never counts! 😛 ).

private async void btnStart_Click(object sender, RoutedEventArgs e)

string result = await TaskEx.Run(() => “A”);
txtResults.Text = result;


Easy as pie 🙂

Hopefully over the next few weeks, I want to think of slightly more complex (and maybe even real world) examples of where await and async might be used. Stay tuned!

Cheesy Puns and Awesome Code

Tonight I attended the Perth .Net User-group, where Joe Albahari was speaking on some of the upcoming features (puns are awesome) in C# 5, specifically the new async and await keywords. It was a very, very interesting night and (thankfully) Joe is a talented speaker, providing clear examples of usage of the new keywords and the concepts and philosophies behind them. There were a couple of really exciting methods in his presentation, one of the ones that stood out to me specifically was around calling several methods with (potentially) different return types and having an enumerable collection of objects at the end of it!! I saw a great deal of potential in this, as well as some of the other .Net 4 related objects (such as Tasks) that he dealt with during his talk.

I will be doing a blog entry this weekend around the new things coming in regards to asynchronous programming in C#5, and things I learn t at the user-group, as well as exploring how these things might be able to help us in our day to day lives.

Night all 🙂

Exceptions and Preserving Exception Types

An interesting thing happened to me today, while working on an enhancement for the application my company supports.

I was tasked with ensuring validation worked on collection of objects, exactly the same as if it were a single object being modified. Normally a straightforward thing, but due to how the application does its ‘thing’ and how it handles different types of exceptions, a little bit of tweaking was necessary, and during the course of this tweaking I noticed a very strange behaviour with my error handling…

We handle/catch exceptions at almost every layer of our application (as opposed to throwing it once and everything magically bubbling up) due to some of the complex processing required when we hit an error. So i was very confused that during the course of debugging my changes today, Exception blocks were not ‘catching’ when being bubbled up. Crazy!

I had the following code in the Click Event Handler of my Save button.

catch (Exception ex)
lblMessage.Text = ex.Message;

What was happening was, the WarningException being thrown from the update of the singular object wasn’t bubbling up as I thought it would into the UI layer. It was completely skipping the WarningException block and being bubbled up into the generic Exception handler in the btnSave_Click event handler.

Took a little bit of investigation, but as it turned out this was being caused by an evil little middle-man between the singular object Update method and the btnSave_Click event… the Update method in my collection object!\

I had the following code in the update method of my collection object.


// Main processing //
foreach (Object r in this.InnerList)
catch (BusinessRuleException e)

throw new BusinessRuleException(e.Message);

catch (Exception e2)

throw new Exception(e2.Message);

You might have noticed that in the exception blocks in the collection object DONT handle a warning exception! So what was happening was, the singular object was throwing a WarningException, however the collection object exception blocks were catching it as a generic Exception, therefore losing the Exception type by the time it was getting to the UI layer it had lost its custom type!

Moral of the story is: when handling exceptions across layers, ALWAYS ensure you handle them consistently across all layers! Or you’ll start losing information!

I realise this isn’t exactly groundbreaking stuff, but i thought it was interesting and worth sharing 🙂

%d bloggers like this: