Rollback SP on Error problems
-
Hi
I have a stored procedure which executes a series of statements inside a transaction. I trap the error after each statement and rollback the tran if unsuccessful.
The procedure selects some data from a view. I executed the statement with the view not yet defined to test the error handling, however the code rollback the transaction
I got the message
Server: Msg 208, Level 16, State 1, Procedure g_sp_produce_active_member_letter_extract, Line 39
Invalid object name 'v_guk_welcome_letter_active_member'.
Server: Msg 266, Level 16, State 1, Procedure g_sp_produce_active_member_letter_extract, Line 65535
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
I've stripped the procedure back to just the select statement and still get the problem
Create Procedure g_sp_produce_active_member_letter_extract
AS
BEGIN TRANSACTION
INSERT Guk_Welcome_Letter_Pending (
Registration_no ,
Title ,
Firstname ,
Surname ,
Address1 ,
Address2 ,
Address3 ,
Address4 ,
Address5 ,
Address6 ,
Post_code ,
Guk_Welcome_Letter_Pending_Attribute_ID 
SELECT v_guk_welcome_letter_active_member.Registration_no ,
v_guk_welcome_letter_active_member.Title ,
v_guk_welcome_letter_active_member.Firstname ,
v_guk_welcome_letter_active_member.Surname ,
v_guk_welcome_letter_active_member.Address1 ,
v_guk_welcome_letter_active_member.Address2 ,
v_guk_welcome_letter_active_member.Address3 ,
v_guk_welcome_letter_active_member.Address4 ,
v_guk_welcome_letter_active_member.Address5 ,
v_guk_welcome_letter_active_member.Address6 ,
v_guk_welcome_letter_active_member.Post_code ,
1
FROM v_guk_welcome_letter_active_member
SELECT @Letter_Err = @@ERROR
IF @Letter_Err <> 0
ROLLBACK
ELSE
COMMIT
I get the feeling I'm missing something obvious here like either somehow telling the procedure not to break on the error
Cheers
-
use ST XACT_ABORT ON and then try executing the query for transactional related errors.
Also i do hope as u said u have to create the view before using it else where.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
Hi Sugesh,
I have tried using SET XACT_ABORT ON both in the first line of the SP and before the executing the SP with no luck.
Yes the views will defnitely be in place, but it seemed like a good opportunity to check the error handling
Cheers
Simon
-
Wouldn't it be easier to check for existence BEFORE?
IF Object_ID('v_guk_welcome_letter_active_member') IS NOT NULL
... your script ...
_____________
Code for TallyGenerator -
The issue here is that since the view is not defined, executing the procedure results in a FATAL error. Fatal errors cause a procedure to abort processing. The definition of fatal errors is not precise, although non-existence of objects will always produce a non-fatal error. Also, any error with a severity of 20 or higher is a non-fatal error. However, identifying which errors are fatal or non-fatal is a moot point since there is no way to gracefully handle a fatal error. Your best bet is to implement defensive coding practices to avoid non-fatal errors.
Having said that, you could test your code by trying to insert a NULL into a field that does not allow nulls. That is a non-fatal error I often use to test error handling.
Gordon Pollokoff
"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones
-
Hi, I have managed to test the code using a few constraint violations successfully.
Of course one should always ensure that objects exist, but since I knew the view didn't exist I thought I'd try to test my error handling using this scenario. But as Gordon has pointed out, this isn't strictly speaking a valid scenario
Thanks for the reminder about Fatal and Non-fatal errors Gordon - That was certainly my problem
Cheers All
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply