raiseerror and transactions

  • if i have a transaction where i am creating a record.

    if an error occurs, do i have to say

    " if @@error raiseerror " so that the transaction is rolled back? or it is rolled back by itself?

    what if there isnt any error but i want the insert to be undone due to some user condition?

    And how do i raiseerror into a log file?

    tx!

  • Depends on error severity. Some errors automatically rollback a transaction, some don't. If you want to be sure, you have to use RAISERROR, or IF condition ROLLBACK TRAN.

    I only know the basics of error trapping, so I'll leave detailed explanation to those who are more proficient in it.

  • If you do not set BEGIN TRANS, the system will roll back the from the current transaction. Otherwise, in order to rollback to desired spot, you need to add some scripts, such as

    IF @@error <> 0

    RAISERROR ...

    ROLLBACK ...

     

  • --Step 1

    --======

    --Create the table and one record

    if exists(select * from sysobjects where name = 't1')

     drop table t1

    create table t1(f1 int, f2 int)

    insert into t1 values(111,888)

    select * from t1

    --Step 2

    --======

    --Execute the following code

    begin transaction

    insert into t1 values(111,999)

    if @@error != 0  -- this condition could be anything, for eg: if @var1 = 'abc'

    begin

     raiserror('Jambus Error1', 16, 1)

     --rollback transaction

     return

    end

    insert into t1 values(111,99999999999999999999)

    if @@error != 0  -- this condition could be anything, for eg: if @var1 = 'abc'

    begin

     raiserror('Jambus Error2', 16, 1)

     --rollback transaction

     return

    end

    commit transaction

    --See that even though the second insert failed, the first insert did not get rolled back.

    select * from t1

    --Now uncomment the rollback lines and execute the script again

    --see the output. The entire transaction is rolled back.

    --Hope it helps. if not please bug me. I will try to clarify.

    --jambu

  • great help, tx a lot!

    now in my case where i am reading a temp table line by line using a cursor, i want in case of an error in one of the inserts, to undo all the inserts of that line only, not the whole temp table.

    This is what i wrote, and it's actually working... unless there is smthg am not seing... I am not using raiseerror, dunno if it is correct. But i am considering to using in order to dump to log file (but still dunno how to use raiseerrir with log )

    do u confirm?

    WHILE

    (@@FETCH_STATUS = 0)

    BEGIN

        BEGIN TRY

             BEGIN TRANSACTION

             INSERT INTO TABLE1 .....

             IF

    @@ERROR <> 0

             BEGIN

                 PRINT 'error1'

             END

             INSERT INTO TABLE2.....

             IF

    @@ERROR <> 0

             BEGIN

                  print 'error2'

             END

             COMMIT TRANSACTION

        END TRY

    BEGIN CATCH

         IF @@TRANCOUNT > 0

         BEGIN

              ROLLBACK TRANSACTION

         END

    END CATCH

    FETCH NEXT FROM table_cursor INTO @oneline

    END /*while*/

  • AWESOME!

    I did not know you were using 2005. I thought you are still living with 2000 like many of us. (ofcourse I have my personal 2005 at home

    2005's exception handling that you are using is perfect.

    Transactions and RAISERROR have nothing to be related with though. These are two different aspects.

    I am only wondering if there will be a performance hit, if I am working with 10000000 (10 million inserts), and start committing for each row. Just a fear though.

    If time permits we could test using Savepoints and commit only for every 1000 or 10000 records for example.

    We used to use such bookmarks while working with COBOL files some 20 years ago.

    If some GURU comments on this aspect, it would be nice. Steve Jones perhaps, if he sees this ofcourse.

    jambu

     

     

     

  • COOL!

    one more question then, since i wont be using a raiseerror, i want to dump the error into a text file. but the user who executes the query doesnt have enough rights to execute xp_cmdshell

    someone proposed that i use raiseerror into log file, but i dunno how to do that in my code since it works perfectly well without it! any other way to dump into a text file?

  • if you wish to log the error into a file, just change the raiserror statement like this

    raiserror('Jambus Error1', 16, 1) with log

    then go to the following file(assuming your SQL Server installation was using the default paths)

    C:\Program Files\Microsoft SQL Server\MSSQL\LOG\ERRORLOG

    bug me as ever, if more questions.

    jambu

  • Yeah but am not using raiseerror

    so what u are saying is that i replace my print by a raiseerror , and the way i wrote the code will still be ok...

    and if i put the level less than 15, i'll log the error and continue to the next line right? i dont want to return the procedure or stop it

  • yep

     

    IF @@TRANCOUNT > 0

         BEGIN

              ROLLBACK TRANSACTION

              raiserror('Jambus Error1', 16, 1) with log

         END

     

     

Viewing 10 posts - 1 through 9 (of 9 total)

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