SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Managing Jobs - Part 4

By Andy Warren,

I've written several articles about jobs over the past few months, beginning with Managing Jobs - Part 1. In this final installment, I want to show you some ideas you can use when deploying larger, more complex jobs.

At some point you reach a level that requires you to move beyond simply putting TSQL or VBScript in a job step. It could be that your task requires more than the 3200 characters allocated for a step, or that you need richer functionality than either language provides, or maybe you need to have the job under source control. In practice I think if you have more than a 1000 characters you should probably think about repacking your code - but it works, so I'll leave that decision to you.

My language of choice is VB6 or VB.Net. The language itself isn't critical, most languages will support features similar to those I'm about to discuss.

The first choice is whether to package your code as a DLL or an EXE. There are two reasons to put your code in a DLL:

  • You want to reuse the code in another job or application (or you are already using it in another job/application). If you can reuse code, don't pass up the opportunity!
  • You want to leverage the very nice feature of VB6 that allows you to suppress any dialogs that might pop 'accidentally' while the dll is running.
  • DLL's are the container for objects, which in turn have properties. If you need to be able to tweak various settings, calling the DLL using VBScript let's you easily do the tweak by setting properties in the job step.

If you put the code in a DLL, there are headaches associated with that decision:

  • You'll have to terminal service to the server to unregister/re-register updates to the DLL.
  • In some cases you'll have to stop/start the SQL Agent to get the change to take affect.
  • If you want to call the code from a procedure, you'll have to use the sp_oa~ series of procedures.

There aren't many downsides to EXE's. You compile, deploy by copying to the server, run. One nice thing about this is that if you want to run it from a procedure, you can just use xp_cmdshell (assuming you have permission to do so). If you need to do some run time tweaking you either have to pass the information in on the command line and parse it (doable, not always intuitive to user new to the application), or put it someplace where the app can read it - registry, table, or ini file maybe.

There are a few things to think about when you write code that will run as a job, particularly if it will run via SQL Agent. I think the most important is that while the application is running as a job, the UI is inaccessible! If your application displays any type of modal dialog (including a message box), the application is stalled. You have to find and kill the process, and/or stop SQL Agent. As I mentioned earlier, if you use VB6, you can avoid this by checking the box shown below:

If you're not clear on this, build yourself a simple 'Hello, World' app that displays a message box. Run it on a test server via the Agent and see what happens.

Just because the UI isn't visible when the application is running doesn't mean you can't have a UI. If your application will occasionally need to be reconfigured, a built it UI for making the changes is a very nice feature. The trick is to make sure that the application runs in the correct 'mode' when you start it. I control the mode of EXE's using command line switches as follows:

  • /RUN        This means run the process and exit automatically when done.
  • /CONFIG    Displays any available configuration settings via a built in form.

If no switch is passed, basically nothing happens. The app just immediately exits.

In VB.Net, I typically build my jobs as console apps. Console apps are meant to mainly run without a UI (though they can have one) and have the ability to write to the 'console' - the old DOS command screen type display. The nice part is that if no command parameters were passed, I write all the possible options to the console - and they show up in the job history. It's possible to write to the console using VB6, but it requires several API calls.

VB.Net also supports setting an errorlevel (remember DOS batch files) using the system.environment namespace. Anything besides a zero will cause the job to indicate it failed. If no valid switch is passed to the command line or something else goes wrong during a valid run, I set the errorlevel to -1 so that the job will fail and I can use the built in notification of SQL jobs to let me know it did fail. Again you can do this with VB6, but it requires an API call.

Logging can be a big deal in a job. For simple jobs you can just run it and rely on sysjobhistory. For intermediate level processes, you might want to log to a standard error/info type table shared by all jobs, something you can scan easily to see what went wrong. At the highest level you might need a separate logging system so that if a process fails, you can figure out what happened and how to get it started again.

Not every process can run as one big transaction, sometimes it's better to get half done and fail, or fail a particular step/segment and continue, than it is to have all or nothing. That's a decision you have to make for each job.

You also need to consider how you'll handle versioning and deployment. Let's assume that the code is under source control - fair assumption? For DLL's, versioning concerns include whether you'll break the interface, do you have other jobs that use the original interface? Even with code under source control, I typically rename the existing dll/exe (process1.exe becomes process1_0701.exe), then copy the new build to the server. Even with source control there is no guarantee that you can recreate the exact build requirement if you had to do so in a hurry, better to have the original binary. You may need to copy various dll/ocx/other miscellaneous files to the server. You can do it manually, but I recommend that you take the time to package every application job into a real installable msi package.

Jobs are one place where patterns pay off in a big way. Build your jobs the same way each time, standardize how they run, how they fail, how they log, and how you deploy. What doesn't seem like a big deal when you write the first job or two suddenly gets to be a very big deal when you have several hundred.

Total article views: 7991 | Views in the last 30 days: 5
Related Articles

Building PowerShell GUIs with Primal Forms

Although PowerShell is best suited for console applications there are times when a GUI interface jus...


Building a Distributed Service Broker Application

Service Broker is one of the more interesting and useful new enhancements in SQL Server 2005, howeve...


best way to inform the user about newly released application.?

best way to inform the user about newly released application.?


BUILDing the Future

Steve Jones talks about the BUILD conference and the future.


Alternative to the Microsoft MMC Console

Are you frustrated by the limitations of the MMC console when managing dozens or hundreds or servers...

sql server 7    
visual basic 6