Problem with Begin/Commit/Rollback Transaction

[naxy]

I'm trying to work in some rollback procedures into a few stored procedures. I've set up the following code. However, when I set it up to fail on purpose, the rollback does not appear to be working (the USP_Write_To_Log is not firing). What am I doing wrong?

Thanks!

DECLARE @err INT

BEGIN TRANSACTION

BULK INSERT Claims_Source_Pre
FROM 'D:\EppsShare\Staging\EPPSPre1'
WITH
(
FORMATFILE = 'D:\EppsShare\lib\Eppspre.fmt'
)

SELECT @err = @@ERROR
IF @err <> 0
BEGIN
EXEC USP_Write_To_Log 'USP_Claims_Prepaid', 'JOB FAILED: Failed to Bulk Insert'
ROLLBACK TRANSACTION
END

COMMIT TRANSACTION

 

[memdiscount]

Hi ;

What I can see here, you have to exit the stored procedure after roll back so that it won't go down and commit the transaction which is already rolled back.

You should return -1 after roll back.

IF @err <> 0
BEGIN
EXEC USP_Write_To_Log 'USP_Claims_Prepaid', 'JOB FAILED: Failed to Bulk Insert'
ROLLBACK TRANSACTION
RETURN -1
END

Thanks


Essa Mughal
Toronto, Canada

 

[Crowley16]

put a print @err just before the if statement to check it's actual value...

--------------------
Procrastinate Now!

 

[memdiscount]

You can also try using CAST(@@ERROR as int), if you are getting any values in @err.

Thanks

 

[SQLSister]

your exec statement needs to be after the rollback or it is part of the transaction rolled back

"NOTHING is more important in a database than integrity." ESquared