Post reply

Missing Commit/Rollback

  • October 13, 2006 at 4:17 pm

    #174705

    I am new to SQL Server, so I'm sure I am making a simple error.

    I get the error 'Transaction count after EXECUTE indicates a COMMIT or ROLLBACK is missing.

    I have an update and an insert. I think I have done a commit if @@Error is zero otherwise rollback after each.

    What might be wrong.

    Also, can you confirm that only a BEGIN requires an END. IF and BEGIN TRANSACTION don't. Is that correct ?

    Thanks

    Robert

  • October 16, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • October 16, 2006 at 12:36 pm

    #666059

    You must be missing a Begin \ End block after an IF statement.

    Can you post the Code ?

    Difficult to give advice without it...

  • October 16, 2006 at 2:02 pm

    #666071

    Thanks for the reply. I think I sorted it out. The error message was misleading. What happened was that I refered to a table name as singular, rather tan plural. That is, I inserted into tblCar instead of tblCars. tblCar does not exist, but check syntax dindn't complain.

  • October 16, 2006 at 4:08 pm

    #666087

    Check out Joe Toscano's suggestions on Try...Catch regarding error messages with SQL2005 instead of using @@error:

    http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1180620,00.html#TRY

     

    P_Insert_New_BookTitle_2K5

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

    create proc P_Insert_New_BookTitle_2K5

    (@TitleName nvarchar(128),

    @Price money,

    @au_fname nvarchar(32),

    @au_name nvarchar(64),

    @CommissionRating int)

    as

    declare @err int,

    @tablename sysname,

    @errormessage nvarchar(2000)

    BEGIN TRY

    begin transaction

    select @errormessage = 'insert into Titles table failed',

    @tablename = 'Titles'

    insert dbo.Titles (TitleName, Price)

    values (@TitleName, @Price)

    select @errormessage = 'insert into Authors table failed',

    @tablename = 'Authors'

    insert dbo.Authors  (au_fname, au_lname, TitleID,

                         CommissionRating)

    values (@au_fname, @au_fname, @@IDENTITY,

            @CommissionRating)

    commit transaction

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION

    -- Log the error

    insert dbo.Application_Error_Log (UserName, tableName,    

                errorNumber, errorSeverity, errorState, errorMessage)

    values (suser_sname(), @tableName, ERROR_NUMBER(), 

                ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE()) 

    RAISERROR (@errormessage, 16,1)

    END CATCH


    Mary Myers

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

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