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 <> 0BEGIN
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
