I received an email from a colleague the other day. He was having some issues with a job running in SQL Agent. Essentially, the code inside one of the steps would raise an error, and the step would immediately end. Now, we all know that there are limited options for any job step (Quit Reporting Success, Quit Reporting Failure, or Go To Step X, as my friend so nicely summarized). Sadly, what was really needed in this case was an option to “Continue Running Step Until Cursor Ends”, or something like that. You can read more about it here.
When he asked me about the problem, I really had no answer, other than to suggest that he would need to alter his code somehow. Of course he pointed out that the code worked just fine outside of the job, as I expected that it would. So, once again I found myself unwittingly defending MSSQL and attacking someone’s code, which probably did not really sit all that well with my frustrated colleague, and rightfully so as his code was much better than anything I would have written.
I looked at the issue, understood the limitations of SQL Agent, and said that the code would need to be changed. My colleague said the code was fine (“The issue is not with the code, it is with the act of using it within a job”), but later acknowledged the code could be altered, and his point was valid as well: why should it be necessary to change? Would it be so hard for SQL Agent to do what was expected (and needed)? Well, apparently it is that hard for SQL Agent, so yes, you need to alter your code. Sorry about that but don’t shoot the messenger…it is my job as a DBA to tell developers that they need to change their code in order for it to work properly, I guess it was just instinct to suggest the same thing to a fellow DBA. And after a code change was realized, my next comments (“so, it really was a code issue”) were less than well received.
Thankfully we can move on and remember one main point. I am an idiot. But many times I have had similar issues from developers and many of them cannot move on (although I am certain they do consider me an idiot). Either they see the limitation in SQL Agent and blame me for one of two things. One, that I cannot alter MS SQL to work with their code, or two, that I do not simply fix the code for them. I guess helping to work through the problem is never enough for some people, apparently they also need someone to tie their shoes every morning. (Be weary of those developers that always wear loafers, the ones you have never witnessed wearing a shoe that needs to be tied. Trust me.) In this case, it was not expected for me to alter MS SQL or to rewrite the code, so perhaps that is why we were able to move on so quickly. But I would like to think it is because we can tie our own shoes.
So, back to the topic at hand…why is it that SQL Agent is not able to work as expected anyway? I usually tell people that it is not an Enterprise job scheduler and that seems to buy me some time. But what about Maintenance Plans? The other day we found that the reindex piece bombs because it tries to reindex a view that has no index. And it is little things like these that can frustrate ordinary developers and senior database administrators alike.
Can’t we all just get along?
