June 15, 2015 at 9:29 am
I have a piece of code that has happily run daily for at least 4 years. I have moved it to a machine that is meant to have an identical set-up, and it fails. I need to work out why it fails, but I am stuck. The code works as follows:
CREATE storedProc1 (Parameter1, etc)
DECLARE @TransactionName nvarchar(127), @TransactionActive bit
-- Plus Other Variables...
BEGIN
BEGIN TRY
SET @TransactionName= 'TransactionName1'
SET @TransactionActive = 'True'
BEGIN TRANSACTION @TransactionName WITH MARK
EXEC @ReturnCode = storedProc2
-- more code
COMMIT TRANSACTION @TransactionName
SET @TransactionActive = 'False'
END TRY
BEGIN CATCH
IF @TransactionActive = 'True' ROLLBACK TRANSACTION @TransactionName
-- More Error-handling code
END CATCH
END
CREATE storedProc2 (ParameterA, etc)
DECLARE @TransactionName nvarchar(127), @OtherVariables...
BEGIN
BEGIN TRY
SET @TransactionName= 'TransactionName2'
SET @TransactionActive = 'True'
BEGIN TRANSACTION @TransactionName
-- More T-SQL code
COMMIT TRANSACTION @TransactionName
SET @TransactionActive = 'False'
END TRY
BEGIN CATCH
IF @TransactionActive = 'True' ROLLBACK TRANSACTION @TransactionName
-- More Error-handling code
END CATCH
END
When I EXECute StoredProc1, it fails with the Error Message "Cannot roll back . No transaction or savepoint of that name was found." I think that message comes from a ROLLBACK TRANSACTION statement in the CATCH of StoredProc2. I have tried commenting out that ROLLBACK statement, which changes the error message to: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 2."
All help gratefully received.
June 15, 2015 at 2:11 pm
If you can manually run the code to troubleshoot it try adding print statements at different levels within both stored procs. That way when you go to execute it you can get outputs of what sections were entered and start to troubleshoot where things are going wrong.
Joie Andrew
"Since 1982"
June 15, 2015 at 2:24 pm
Try moving your variable declarations/assignments outside of your TRY/CATCH blocks. I'm guessing that the behavior of variable assignments in TRY/CATCH blocks is different between the two versions of SQL Server.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 15, 2015 at 6:23 pm
Get rid of @TransactionActive. And don't use @@TRANCOUNT either. And since you're not using SAVE TRANSACTION, the names on the TRANSACTION statement are superfluous and can be removed.
The proper way to check for an active transaction is via the XACT_STATE() function:
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
...
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply