Post reply

Urgent - Locking and Exceptions

  • June 8, 2005 at 2:34 am

    #362879

    Does anyone know if it is possible to catch an error in an SQL job and not have it reported ?

    For example, we want to trap and 1222 error for a lock timout, act on it but not have the SQL job that calls the code fail.

    TIA

    cheers

    dbgeezer

  • June 8, 2005 at 4:15 pm

    #564013

    any ideas anyone ?

    cheers

    dbgeezer

  • June 9, 2005 at 8:29 am

    #564235

    Verify this article if it apply to you

    http://support.microsoft.com/default.aspx?scid=kb;en-us;286286

  • June 9, 2005 at 8:47 am

    #564243

    thanks for the link -

    the problem relates to some code written by anotehr guy in the team so i've passed on your link -

    the 1222 error is caught on our servers so it may not be connected.

    the problem wasn't really trapping the error - he wants to trap it and then deal with it but then stop sql server reporting the 1222 exception back to whatever has called it. easy enough in software but we need to get an sql job to ignore it, otherwise the job will fail.

    cheers

    dbgeezer

  • June 9, 2005 at 9:02 am

    #564249

    Try using number of re-trys and donot report error properties of SQL server job.

    Or

    Create an application with C#, VB which can very well handle these errors and call the application from SQL Server Job.

    Regards,
    gova

  • June 9, 2005 at 9:05 am

    #564251

    We need it to report other errors though.

    Creating an application would be ideal but not in the environment we work in. Might be a way forward in the future though.

    However, the original question still remains. Thanks for your input.

    cheers

    dbgeezer

  • June 9, 2005 at 5:50 pm

    #564435

    I haven't look into the error number and what severity level it is, but if the severity level is high enough, it will just terminate whatever you are doing - ergo you can't capture it in SQL Script and deal with it.  I was dealing with corruption for hours last night - same deal, you get stopped in your tracks.

    Maybe you could have it use an xp_cmdshell to call a OSQL which runs the SQL in your job - the output from OSQL can be caught by your script (it would be running in a separate connection so the error message there won't stop it) and you can parse it to determine if you wish to press ahead, re-execute or abort yourself.

    Cheers

Viewing 7 posts - 1 through 6 (of 6 total)

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