The next time someone on the team needed a job, they could simply refer to source control. There they were all, awaiting some DBA to use them. Said DBA opens the file, and attempts to load the job on a system, only to encounter an error. Odd. Face goes into that half scowl, half frown as they attempt to dig into the job code and understand why it failed. After a few stabs at it, its realized that this code will not work as is. How many other jobs will not work? how long have we been backing up jobs via script and it not working? Well, a smart man would say, since we introduced the automation to save us time. Back to the drawing board to find out what is wrong. In a quick overview, a couple things were wrong with these scripts. There is an sp_Add_Category proc call that was missing a named parameter. @name was not referenced, and the command failed if the category was not present. The servername was included sometimes, and not others. But the worse was that the job steps were not in the proper sequence. The script would attempt to create step 2 before step 1, and, well, SQL Server didnt seem to like that. So, any job that had more than 1 step, was potentially scripted incorrectly, and useless. The other issues were issues, but this step thing was a show stopper. Yeah, back to manual job script generation we go. I can actually script out 100 jobs in about 8 minutes, if i am not interrupted. Click the job, hit F2, copy name to clipboard, hit escape, right click job with mouse, hit S, then C, then F, which will select menu options to Script to file, paste name into File dialog, hit enter, move to next job, repeat. Nice and automated, huh? Not!
A coworker started digging at the VBScript code, as well as other options. After a bit of time, made the discovery that the code was ultimately hitting a table (sysjobs) that had its statistics out of date. Another index was being used, step name, and this was determining the order for the output of the DMO code. After updating statistics on the sysjobs tables, viola! Steps were now in order. Wow. Odd. Wow.
So, we set to talking about this. We had auto statistics configured on this database. However, statistics dont get kicked off until a percentage of the table changes. There are 100 jobs on the system, and they have been there for a long time. We may add 1 or 2, maybe 5 in a year. Not a lot of change. So, I could see how they may have gotten out of whack, and we not even really known, since its such a lowly piece of the system. But with a weird side-effect.
With statistics updated on the systems, and some slight modifications to the VBScript code, we once again have an automated approach to generating Job Scripts for each job, so that they can be saved in Source Control. And this time, we are testing the scripts generated, to ensure that they actually create jobs, and can execute successfully. No more assuming they are good, only to find all the steps out of order.