Anyone that knows me knows how much I love Operations Manager 2007. We met a few years back, flirted a little bit, and now we find ourselves together each and every day. And honestly, everything is great, it really is. Well, most of the time is is great. As with any relationship, the more time you spend together, the more likely something is going to bother you. Perhaps it is how they leave dishes in the sink, or dirty laundry on the floor, or the fact that they choke when trying to discover SQL Agent jobs that have null descriptions.
Such was my headache today. I have recently put together a custom monitor for SQL Agent jobs. The idea was to have OpsMgr monitor all jobs to see if they have been created or modified in the past day. Why? Well, why not? One of the reasons I enjoy using OpsMgr is that it is very flexible. I am able to customize it to do just about anything. And as much as I enjoy that flexibility, it comes at a price.
I liken it to a big tinker set. Essentially all the pieces I need are there on the floor, scattered about, and I am the one that needs to put it all together with no instructions.
Anyway, back to the problem I found. After I got all the pieces together, everything was working fine, it really was. And then I noticed something strange inside the state view for SQL Agent.
Huh? How is this one server not being monitored? What gives? I have 130 servers and 129 are working as expected, so what is so different about this one? Well, I have no idea, so I decide to repair the agent. After the repair fails to clear it up, I decide to uninstall the agent, wait for everything to clear out from the OpsMgr console, and then reinstall the agent. I came to work today (expecting? hoping? praying?) that everything would be working only to see that nothing had changed.
So, time to fish. I start by going to the alerts inside the OpsMgr console. I find a warning that the SQLAgentObjectDiscovery had an issue for this box. Okay then, I head to the box to examine the OpsMgr event log directly. There I find the same cryptic message:
C:\Program Files\System Center Operations Manager 2007\Health Service State\Monitoring Host Temporary Files 1\4341\SQLAgentJobDiscovery.vbs(105, 5) Microsoft VBScript runtime error: Type mismatch: ‘AddProperty’
Okay then, I open a command line, navigate to that directory, and execute the command, hoping to see the error. I see no error, so if you ask me the script is working now, right? Well, no, because if it was then I would be seeing it reflected in the OpsMgr console. So something is still amiss. I decide to dig into the script itself, to see how the discovery is being done. I find two things of interest. First, it is doing ‘sp_help_job’ to return information about all the jobs on the server. Second, it is adding properties to a custom class for the data being returned.
A colleague happens to be standing with me and suggests that I try to run the procedure myself, as perhaps that is having an issue. I execute sp_help_job and get back results. I also comment out the “On Error Resume Next” statements in the vb script, so that if an error happens we should(!) see them pop up. Nothing happens.
Then, as if I am working a crime scene, I get a flash…
Type mismatch
‘AddProperty’
(105,5)
I go back into the script, to line 105. That line is trying to add the property based upon the description field returned from sp_help_job.
Do you know what the default description is for a job created through the GUI? Well, if you create a job through the GUI and do not put in a description, one gets created for you. It is “No description available.” Go ahead and try it. Did you try it? Good. Now, from SSMS, script out the job instead of creating it, and examine the code in a new window. No description is listed. So, internally, MS SQL is inserting the text “No description available.” What does this mean? I will tell you what this means.
The result set I get when executing sp_help_job has five rows with NULL descriptions. That’s right, NULL. As a result, the SQL Agent job discovery chokes, and therefore the OpsMgr console does not display any information. I update the jobs to have “No description available” and a short time later the OpsMgr console was working perfectly. So, this vendor product had to go out of its way to insert a NULL description. Then, someone at Microsoft built a collection that chokes on NULL values. Combine those two things and you get a day of my time lost trying to figure out how to put the little spoke into the wheel.
Crazy.
