Trasaction Stops after first error

[digitallyskilled]

I am having a problem with the flow of a transaction.
as soon as it finds an error then the transaction stops. the next line says

SET XACT_ABORT ON
DECLARE ErrorCode INT

INSERT TABLE 1
IF @@Error <> 0
SET @ErrorCode = @@Error
PRINT '@ErrorCode'

IF @ErrorCode = 0
BEGIN
INSERT TABLE 2
IF @@Error <> 0
SET @ErrorCode = @@Error
END

IF @@Error <> 0
BEGIN
PRINT 'Rolled Back'
ROLLBACK TRANSACTION
ELSE
PRINT 'Committed'
COMMIT TRANSACTION

SET XACT_ABORT OFF
the problem is it never gets to Print '@ErrorCode' on the first table. But it does roll the transaction back which it is supposed to do. As soon as it reaches one failure it ignores everything else.

Any ideas how i can keep it going so I can log all of the errors at the end.

 

[SQLDenis]

some errors are fatal and not trappable
SQL server 2005 will have much better capabilities to take care of problems like that

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[mrdenny]

In SQL 2000 only specific error levels can be recovered from. It depends on the error level that you are receiving when you run your code.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[digitallyskilled]

How can I tell what level of error I am getting, and how do I know if that error can be recovered from? Is there a list somewhere?

 

[SQLDenis]

lookup severity in BOL



“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[mrdenny]

When it gives you the error it will tell you the severity. Look up Severity in BOL and it will give you the list and what they all mean.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com