Post reply

Try Catch Failed??

  • May 3, 2011 at 9:09 am

    #97754

    I am using Bold for actual code, Italic for pseducode and what happened is in parentheses

    I have this procedure:

    -------------------------------------------------------------------------------------

    ...

    Insert into Job Table (Worked)

    Select index value into variable (May or may not have worked)

    Begin try

    Use a View (The view was dropped by a user :crazy:)

    ...

    End try

    Begin catch

    Update Job Table with the error message. (did not happen)

    End Catch

    ----------------------------------------------------------------------------------------

    I know the catch block would have worked, if it was called.

    So, why was the catch block not called?

    Would the loss of a view cause the database connection to close?

    Thanks,

  • May 3, 2011 at 9:13 am

    #1319153

    The error is being thrown at parse time , so the try catch block is not actually being entered.


    Clear Sky SQL
    My Blog[/url]

  • May 3, 2011 at 9:27 am

    #1319164

    Syntax errors (including missing objects) won't trigger a try/catch.

    From Books Online:

    The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

    * Compile errors, such as syntax errors, that prevent a batch from running.

    * Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

    These errors are returned to the level that ran the batch, stored procedure, or trigger.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 3, 2011 at 9:27 am

    #1319167

    Then, why did it insert into the job table?

  • May 3, 2011 at 9:30 am

    #1319171

    Deferred name resolution. At parse of the batch the object didn't exist, so the statement was not bound or optimised. Just before the statement ran, SQL tried again to parse, bind and optimise the statement and again the parse failed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 3, 2011 at 9:32 am

    #1319174

    Thanks for your help.

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

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