May 23, 2004 at 11:50 pm
I have an sp.
CREATE PROCEDURE .....
AS
BEGIN TRANS
/*task 1*/
DELETE FROM ITEM_MASTER WHERE ITEM_CODE=@ItemCode
IF @@ERROR <> 0
GOTO ERR_HANDLER
/*task 2*/
BULK INSERT ITEM_MASTER FROM ...
IF @@ERROR <> 0
GOTO ERR_HANDLER
COMMIT TRANS
RETURN(0)
ERR_HANDLER:
ROLLBACK TRANS
RAISERROR('Transaction Failed',16,1)
return(-1)
END
The problem is for some kind of errors (runtime) control transfers to the IF statement and the transaction is rolled back, but for some other errors (runtime) execution terminates at the bulk insert statement. since the execution doesn't get to ERR_HANDLER: the ROLLBACK TRANS statement never gets executed, resulting in a curropted database.
Is there any way to solve this problem.
Thanks in advance.
May 24, 2004 at 12:01 am
Put A SET XACT_ABORT OFF and then test your procedure.
May 24, 2004 at 3:13 am
this was given to me by a friend at work:
select @GlobalError = coalesce(nullif(@GlobalError, 0), @@error)
if @GlobalError <> 0
begin
goto ErrHandler
end
if the SP bombs out spectaularly, @GlobalError still = 0, but @@error will have the error value of the proc bombing out, which we then capture in the coalesce trap and act on…
apparently it will trap errors from bombed out operations - haven't tested it yet but have been assured it works
cheers
dbgeezer
May 24, 2004 at 5:14 am
Read an excellent article about error handling, by SQL Server MVP Erland Sommarskog:
http://www.sommarskog.se/error-handling-II.html#SP-check
Razvan
May 25, 2004 at 8:49 am
Do the bulk insert first. You can bulk import to a staging table. If the bulk insert fails because of dirty data, the rest of the stuff won't get executed and you're no worse off than before.
If the bulk insert works, then you can do your delete, and then INSERT...SELECT from the staging table to the real table.
May 25, 2004 at 10:22 pm
i have also faced this problem
Statements given below should trap the error but will not
i have gone through bol where there is written about severity of error but not much has been elaborated if anybody can trap such errors then please reply
DEclare @test-2 int
Declare @test1 char(1)
Set @test1='C'
set @test-2=@test1
If @@Error<>0
Print 'Error'
Rohit
June 4, 2004 at 10:10 am
The same issue is getting for us also. if some one who has been worked on this before please help us out.
Venkatesh
venkatesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply