Post reply

in line error handling for sql agent jobs

  • August 23, 2004 at 11:18 am

    #108588

    Hey everyone,

     

    Here's my question and thoughts out loud.

    I am running a job that calls a sql lite speed job to back up some transaction log files.

    The lite speed job sometimes will raise an error but handle it and then continue on with the script to completion.

    The sql agent flags the error and then bombs the job.

    So what I was wondering, is it possible to create in line error handling with in a job so that it can resume the job.

    An analagy would be the 'on error resume' type of syntax that you can use in vb script.

    I haven't seen too much of this for sql documentation for the agent and jobs.

    I was wondering if anyone had any thoughts or tricks that they could share with me on this.

     

    Thanks in advance for your consideration,

    Max

     

  • August 24, 2004 at 6:26 am

    #520298

    I haven't tried this in the job context, but I've noticed in our VB world that if I call SP 1 that performs an exec on SP 2 that internally crashes due to some SQL error that VB does not acknoledge the error. I've coded around this by using an if @@ERROR -> RAISERROR check within SP 1 immediately after the exec to SP 2. So, assuming the "SQL Lite Speed" SQL can be run from an SP (I just now took my first 2 minute look at their web site) you can front-end that SP from another one. If a "non-handled" error occurs you could raise a specific error either through a unique error level if possible or a local temp table or something that could be acknoledged within the top level SP which could then perform a RAISERROR for the job to acknoledge. Otherwise the top level SP simply finishes successfully.

    If you are using the command level interface to "SQL Lite Speed" (which they apparently have) you should be able to just call it from a command script that resolves its own final error level.

     

  • August 25, 2004 at 9:01 am

    #520539

    Thanks Aaron,

     

    I appreciate your insight and feedback.

    I will give your strategy a shot.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply