Transaction Error
-
I am getting an error when i issue a save transaction "Cannot issue SAVE TRANSACTION when there is no active transaction"
I am using this in a sp. This is what I am trying to do...
BEGIN TRAN ABC
INSERT INTO tblMaster
IF ERROR ROLLBACK TRAN ABC
GET SCOPE_ID
INSERT INTO tblDetail
IF ERROR ROLLBACK TRAN ABC
SAVE TRAN T1
UPDATE tblTemp
IF ERROR ROLLBACK TRAN T1
COMMIT TRAN ABC
-
If there is an error in the first item, a rollback will occur and there is no transaction. You don't want to do the save if the first rollback occurred.
-
My Bad,
All the Rollback and Save Transaction are in IF ELSE statements. I had a SET NOCOUNT ON which was causing a problem, something like
NO COUNT ON
BEGIN TRAN ABC
INSERT INTO tblMaster
IF ERROR ROLLBACK TRAN ABC
GET SCOPE_ID
INSERT INTO tblDetail
IF ERROR ROLLBACK TRAN ABC
ELSE SAVE TRAN T1
UPDATE tblTemp
IF ERROR ROLLBACK TRAN T1
COMMIT TRAN ABC
NO COUNT OFF
The problem is when i use the keyword GO after SET NOCOUNT ON I get errors "define param..." and when i take the keyword GO out I get the "Save Transaction" error.
Confused
Thanks
-
Where is "Begin Tran T1"..
It appears you are going from Tran ADC to Saving Tran T1
-
SET NOCOUNT ON | OFF can be used anywhere as many times as you want.
GO is a batch separator, so any local variables created with DECLARE are release when you issue a GO. Since your problem seems to be related to the structure of your code, you should be as precise as possible. At the very least, show the actual IF ELSE structure, including BEGIN and END. Post the exact code with any changes to table names, literal data, etc., as need for confidentiality.
I assumed you used the following format:
SET NOCOUNT ON
BEGIN TRAN ABC
INSERT INTO tblMaster
IF ERROR
ROLLBACK TRAN ABC
ELSE BEGIN
GET SCOPE_IDENTITY()
INSERT INTO tblDetail
IF ERROR
ROLLBACK TRAN ABC
ELSE BEGIN
SAVE TRAN T1
UPDATE tblTemp
IF ERROR
ROLLBACK TRAN T1
COMMIT TRAN ABC
END
END
SET NOCOUNT OFF
Mike
-
Here is what I have done.
CREATE PROC usp_Insert
@param 1 varchar(100), @param2 int, @param3 tinyint
AS
SET NOCOUNT ON
DECLARE @errStatus VARCHAR(100)
SET @errStatus = ''
BEGIN TRANSACTION ABC
INSERT tblMaster
IF @@ERROR <> 0
BEGIN
SET @errStatus = some value
ROLLBACK TRANSACTION ABC
GOTO ERRORHANDLER
END
ELSE
BEGIN
SCOPE_IDENTITY()
END
INSERT tblDetail
IF @@ERROR <> 0
BEGIN
SET @errStatus = some value
ROLLBACK TRANSACTION ABC
GOTO ERRORHANDLER
END
ELSE
BEGIN
SAVE TRANSACTION T1
END
UPDATE tblTemp1
IF @@ERROR <> 0
BEGIN
SET @errStatus = some value
ROLLBACK TRANSACTION T1
GOTO ERRORHANDLER
END
ELSE
BEGIN
SAVE TRANSACTION T2
END
END
UPDATE tblTemp2
IF @@ERROR <> 0
BEGIN
SET @errStatus = some value
ROLLBACK TRANSACTION T2
GOTO ERRORHANDLER
END
COMMIT TRANSACTION ABC
ERRORHANDLER:
SET @errStatus = some processing
RETURN @errStatus
SET NOCOUNT OFF
The thing that comes to my mind is would this transaction commit if there is a rollback to T1 ot T2 or would i need to change error handling to accomodate for that. I am explicitly committing transaction abc only once in the entire procedure.
-
Yes, if an error occurs in UPDATE tblTemp1 (after saving T1), then you jump to your error handler without committing or rolling back transaction ABC. Same thing could happen with an error on UPDATE tblTemp2.
Offhand, I don't see how you could get to the SAVE TRANSACTION statements without the transaction being given the code you've provided.
The purpose of SAVE TRANSACTION is rollback only part of transaction. If you immediately jump to your error handler and rollback everything (ROLLBACK TRAN ABC), there is no need for SAVE TRANSACTION.
If you want to commit ABC if the tblMaster and tblDetail inserts complete successfully (no matter what happens with the tblTemp1 and tblTemp2 inserts) and use the value of the identity column from the tblMaster insert as part of the tblTemp1 and tblTemp2 inserts, I would use two separate transactions. During the first transaction, save SCOPE_IDENTITY() into a local variable after the first insert.
Begin a second transaction for the tblTemp1 and tblTemp2 inserts, using the SCOPE_IDENTITY() local variable mentioned above.
The advantage of two transactions is that it frees up tblMaster and tblDetail a little sooner.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply