asked
Can't create a Nested Transaction
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 am stuck. The code works as follows: CREATE storedProc1 (Parameter1, etc) DECLARE @TransactionName nvarchar(127), @OtherVariables... BEGIN BEGIN TRY SET @TransactionName= 'TransactionName1' BEGIN TRANSACTION @TransactionName WITH MARK EXEC @ReturnCode = storedProc2 ..... END TRY BEGIN CATCH -- Error-trapping code END CATCH END CREATE storedProc2 (ParameterA, etc) DECLARE @TransactionName nvarchar(127), @OtherVariables... BEGIN BEGIN TRY SET @TransactionName= 'TransactionName2' BEGIN TRANSACTION @TransactionName -- More T-SQL code ..... END TRY BEGIN CATCH -- Error-trapping code END CATCH END ---------------------- I have tracked down that the code fails at the second BEGIN TRANSACTION statement on the new server, a command that was happily accepted on the old server. Is there some setting such as "Allow Nested Transactions = True' that needs to be set on the new machine. (The new machine is a brand new install, built last Friday.)
Please note that my code has got garbled in the process of posting... Apologies!
Hi Mark - the code that you have posted is that of CREATE of two stored procedures. Are you saying that you are unable to run the CREATE statement on the new server, or is it related to the EXECution of the stored procedures. Also, can you post the exact error messages you are getting?
The problem occurs when I EXECute the first SP. It terminates at the BEGIN TRANSACTION statement in the second, with this 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. 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." I hope that gives some helpful clues.
KenJ: I just have posted the error message, a few minutes before you commented. Hopefully, you just need to refresh your browser to see it.
You cannot rollback a nested transaction that is also a named transaction. From books online ([
https://msdn.microsoft.com/en-us/library/ms188929.aspx][1]) : Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. **A rollback to any other name (other than a valid savepoint name) generates an error.** None of the statements executed before the rollback is, in fact, rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back. Something is causing your procedure to drop into the CATCH block where it didn't before. To see why storedProc2 is dropping into the CATCH block, I would remove the transaction from storedProc2 completely and add the following to its CATCH block (from here - [
https://technet.microsoft.com/en-us/library/ms179296%28v=sql.105%29.aspx][2]): SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; I would probably leave the transaction out of storedProc2 completely since SQL Server doesn't really do nested transactions. You can read more about this misconception here - [
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/][3] [1]:
https://msdn.microsoft.com/en-us/library/ms188929.aspx [2]:
https://technet.microsoft.com/en-us/library/ms179296%28v=sql.105%29.aspx [3]:
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2630-nested-transactions-are-real/
16 People are following this question.
