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