Running SS2005. I have a script that calls
procedureA which in turn calls
procedureA has no explicit transactions.
procedureB is split up into several small blocks of non-nested BEGIN and COMMIT TRANSACTION.
I have peppered
procedureB with PRINT statements to show me the value of
@@TRANCOUNT. It's always at 0 or 1.
But when I check
master..sysprocesses it shows open_tran at 1 or 2. Where this extra transaction is coming from?
I am posting the 2 procedures below.
pr_Get12Relatives has been stripped of most of its content for clarity as it contains 1600 lines! It just repeats the same behavior as it searches for different types of relationships.
procedureA = pr_GetRelatives
procedureB = pr_Get12Relatives