SP Runtime error - Transaction count mismatch

  • Hi

    I have this SP where the table is name used is wrong which is intentional

    CREATE PROC test_delete

    AS

    BEGIN

    BEGIN TRY

    BEGIN TRAN

    INSERT INTO table1

    SELECT 1

    COMMIT TRAN

    END TRY

    BEGIN CATCH

    ROLLBACK TRAN

    END CATCH

    END

    I got this error

    Invalid object name 'table1'.

    Msg 266, Level 16, State 2, Procedure test_delete, Line 7

    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

    thanks

    ek

  • is there a trigger on the real table represented by "table1"?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • No trigger on the table, in fact no table exists as such.

    I don't understand the transaction error, shouldn't it directly jump to the catch block and rollback the transaction as soon the error encountered ?

  • Can anybody pls help on this issue ?

  • ek-822869 (11/10/2011)


    Can anybody pls help on this issue ?

    The full error message you are getting is this one:

    Msg 208, Level 16, State 1, Procedure test_delete, Line 6

    Invalid object name 'table1'.

    Msg 266, Level 16, State 2, Procedure test_delete, Line 6

    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

    Try/catch doesn't work with error 208 (Invalid object name). Actually nothing does. Even if you try to check for @@ERROR variable after the error line - it will show you nothing. You can run this:

    CREATE PROC test_delete

    AS

    BEGIN

    BEGIN TRY

    BEGIN TRAN

    INSERT INTO table1

    SELECT 1

    PRINT 'TRY'

    COMMIT TRAN

    END TRY

    BEGIN CATCH

    PRINT 'CATCH'

    ROLLBACK TRAN

    END CATCH

    END

    You will never see 'TRY' or 'CATCH' printed. This is because an execution inside a stored procedure immediately stops when error 208 is raised. The next command which will be executed is the one after your 'EXEC test_delete' statement.

    And you can do nothing to change this behaviour. So my advices would be:

    1. Don't try to use objects if they are not exist

    2. In your application after each SQL batch check @@TRANCOUNT and if it's greater than 0 do ROLLBACK


    Alex Suprun

  • Thank you very much :-). Its interesting to know that the Try catch doesn't work with all the errors. Is there any specific list of error numbers that Try Catch cannot handle ?

  • Thank you very much :-). Its interesting to know that the Try catch doesn't work with all the errors. Is there any specific list of error numbers that Try Catch cannot handle ?

  • 'INSERT INTO' calsue expect table to EXIST but 'SELECT INTO' doesn't. Try the code below.

    CREATE PROC test_delete

    AS

    BEGIN

    BEGIN TRY

    BEGIN TRAN

    --INSERT INTO table1

    SELECT 1

    INTO table1

    COMMIT TRAN

    END TRY

    BEGIN CATCH

    ROLLBACK TRAN

    END CATCH

    END

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

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