help needed in error handling and undo transaction
-
I am reading a temptable, and doing 2 inserts. In case of error, i want the 2 inserts to be undone, and move to the next line. The complete opposite is happening and the process is being stopped while i wanr it to move on!Help appreciated!
This is my code:
BEGIN TRANSACTION
if exists(select [id] from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#textfile'))
drop table #textfile
CREATE TABLE #textfile (line varchar(8000))
BULK INSERT #textfile FROM 'c:\init_newsl.txt'
DECLARE table_cursor CURSOR FOR SELECT line FROM #textfile
OPEN table_cursor FETCH NEXT FROM table_cursor INTO @oneline
SET XACT_ABORT ON
WHILE (@@FETCH_STATUS = 0 AND @oneline != '')
BEGIN
INSERT INTO mytable1 values(@f1, @f2)
IF @@ERROR <> 0
BEGIN
PRINT 'Error in insertion of table1. Error is ' + LTRIM(STR(@@ERROR))
RAISERROR('',15,1)
goto next_line
END
INSERT INTO mytable2 values(@f3, @f4)
IF @@ERROR <> 0
BEGIN
PRINT 'Error in insertion of table2. Error is ' + LTRIM(STR(@@ERROR))
RAISERROR('',15,1)
goto next_line
END
goto next_line
next_line:
FETCH NEXT FROM table_cursor INTO @oneline
END /* while fetch status = 0 */
-
do you mean
- perform update 1
- perform update 2
- check for error
if error, undo updates 1 and 2
if not, do nothing
- do update 3
continue?
If so, wrap updates 1 and 2 in a transaction and commit or roll it back on the error check. The move to update 3.
-
What I mean is:
while reading temptable
- perform update 1 on real table AND check if error
- perform update 2 on real table AND check if error
If error in any of them, roll back both and go to next line in temptable
But i don't actually know how to write it differently then what i did
-
I think you've to begin your transaction in loop of temp table
or
if you are using SQL 2005
Check for
--start loop for temp table
BEGIN TRY
BEGIN TRANSACTION
--your code here
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH;
--end loop for temp table
-
am actually using 2005 and it worked
tx a lot!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply