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 */

Hi Terry,

You need to begin a transaction for each unit of work that you with to either commit or rollback. In your case, you are encapsulating the entire process in the transaction by placing your begin outside of the individual fetch statements. Also, I can't see a commit/rollback anywhere.

I would question your need to use a cursor here - can you post what you're trying to do and maybe we can help?

Anyway, if you did want to go down the cursor route, you would need to:

WHILE (@.@.FETCH_STATUS = 0 AND @.oneline != '')
BEGIN
BEGIN TRANSACTION t1

INSERT INTO MyTable1...

IF (@.@.ERROR <> 0)
BEGIN
ROLLBACK t1
GOTO NextLine
END

...etc

NextLine:
IF (@.@.TRANCOUNT >= 1) -- or >= 2 if you've a parent tran...
COMMIT t1

FETCH...
END

Cheers,
Rob