Nested transactions question.

Hope I am posting in the right forum. If I understand correctly, a ROLLBACK TRAN statement rolls all transactions (if they are nested) back to the original BEGIN TRAN.

I have a situation when an SP uses a transaction, performs a series of operations inside that transaction, including a call to a different SP, which uses a distributed transaction. If the transaction inside the child SP fails and needs to be rolled back, I get a warning message saying that the tran count on the way out is less than that on the way in. No problem, since it's not fatal. But the problem manifests when I attempt to use the SQL Agent to schedule a job to run the parent SP. It fails on that warning message, interpreting it as an error.

I was thinking of disabling the distributed transaction inside the child SP, and just have the transaction in the parent SP, which, again if I understand correctly, should be escalated to a distributed transaction once the child SP is called. The child SP will raise an error (if it's a real error) and then the trnasaction in the parent SP will handle the rollback of everything.

The reason for this elaborate setup is that I need to cycle through a cursor (yes, I know, sloppy, can't see an alternative) in the parent SP, and each iteration begins and commits (or rolls back) a transaction.

Wil this work? Can anyone suggest a better way?

will this help.

select name
into #tb
from master..systypes
go
select * from #tb
go
declare cc cursor forward_only
for select name from #tb

declare @.name sysname
open cc
fetch next from cc into @.name

while @.@.fetch_status=0
begin
begin tran
delete #tb
where name=@.name and name!='sysname'
if @.@.error=0
commit tran
else
rollback tran

fetch next from cc into @.name
end
close cc
deallocate cc
go
select * from #tb
select @.@.trancount
go

if object_id('tempdb..#tb') is not null
drop table #tb
go

|||Hmmm.... Not sure I understand. This is more or less the code I have. Except for where you have "DELETE #tb WHERE..." I have "EXEC spMyStoredProc @.myInputParam". and inside "spMyStoredProc" there is a distributed tran. So the @.@.TRANCOUNT at the beginning of spMyStoredProc is equal to 1. But inside the SP, let's say the transaction fails for whatever reason, and a ROLLBACK is called. The @.@.TRANCOUNT is set to 0 by the ROLLBACK statement. And now @.@.TRANCOUNT at the end of the SP is less than it was at the beginning. That generates a warning message. But apparently the SQL Agent that handles the Job execution treats it as an error. Or the error that caused the ROLLBACK was raised - I am not sure. In any case - Job ends. But I need it to continue. If I just run the code from QueryAnalyzer - it's ok. It generates warnings, but runs through all the cycles. How can I avoid being bounced out of a SQL Job?|||

(Hope I am posting in the right forum. If I understand correctly, a ROLLBACK TRAN statement rolls all transactions (if they are nested) back to the original BEGIN TRAN.)

That is actually not correct if you use Transaction Save Points which you can roll back as needed after service pack 3a of SQL Server 2000. Now to your question add Transaction save points to any Transaction you don't want rolled back with the error code. Run some tests, transaction is a unit of work but the vendors like Microsoft added Save Points which enable nested Transaction without a roll back to one, with Save Points if you fail at number 100 you will have 99 completed. Run a search for Save Points in the BOL. Hope this helps.

|||

Thanks for pointing out the SavePoints. I did research tehm, but I am not sure if this applies here. I guess I am not explaining the issue clearly enough. I DO want the transactions, both of them, rolled back if an error is encountered in either. However, I have a scenario where the parent transaction is in a cursor (just like that code sample above shows). Each cursor iteration begins and ends a transaction. Each of those transactions calls an SP with another (child) transaction. If anything breaks, I want the transactions to roll back, then I want to go on to the next cursor iteration. If I execute the code in QueryAnalyzer - it works fine. If I schedule the execution via an SQL Job - the Job abends on the first error, rolls back the transaction but does NOT go on to the next cursor iteration. My guess is, this is because the warning that is raised by the child SP (standard non-fatal warning that tran count at the beginning of the SP does not match that at the end) is treated as a fatal error by the SQL Agent, and it terminates the Job.

I could be wrong, and it could be the actuall error that caused the transaction rollback in the first place is being raised, and the Agent may be reacting to that. Whatever the case - My Job stops the cursor execution and exits with an error. Which is something I would like to avoid.

|||

If you can run it and the Agent is not running it, that maybe permissions related when you run it is it running under your admin level permissions but when the Agent runs it, it is running under the Agent's permissions so it will not continue after an error in transactions. You can enable xp_cmdshell in the Surface area configuration tool then create a proxy account for the Agent with your admin level permissions. Run a search for Agent proxy account in the BOL, xp_cmdshell is finally documented but disabled by default so you need to enable it in the Surface area configuration tool. If the permission does not solve the problem then you need to separate your transactions into blocks and connect them with sp_executesql or exec statements. Hope this helps.

|||Thanks. My agent is running with admin permissions already, though :-( Final question - my child SP is written in such a way that I can pass it a flag that governs whether or not I want to use transactions (did that for testing). If I disable the child transaction (its a distributed tran), will a rollback on the parent also roll back the changes made by the sub-called SP? I think that the parent transaction will be automatically escalated to distriburted tran in this case, but I am not sure. I can make it into a distributed transaction manually, if needs be. From what I can see - this would be my only option unless I want to duplicate a lot of code.|||

(will a rollback on the parent also roll back the changes made by the sub-called SP?)

No if each is separated by save points and if you are not using MSDTC maybe you should look into it.

|||

They are not separated by save points.

Main SP code:

Code Snippet

OPEN curCustomersToCancel
FETCH NEXT FROM curCustomersToCancel INTO @.CustID
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN DISTRIBUTED TRAN

EXEC @.ReturnCde = spCancelServices @.CustID
If @.ReturnCde <> 0
Begin
IF(@.@.TRANCOUNT > 0) ROLLBACK TRAN

Select @.SpErrDesc = @.retmsg
PRINT '******* @.SpErrDesc = ' + @.SpErrDesc
BREAK
End

Else

Begin

IF(@.@.TRANCOUNT > 0) COMMIT TRAN

End

FETCH NEXT FROM curCustomersToCancel INTO @.CustID

END
CLOSE curCustomersToCancel
DEALLOCATE curCustomersToCancel

The code in spCancelServices has no transactions - it's just a series of SQL statements. Will this properly roll back all modifications made inside spCancelServices in case of an error?

|||

In the link below download the file SQLServer2005_US_ALL on the left side and in there check samples for chapter 13 and 14 in the notepad files and the PDF note that one trigger in chapter 13 can do what you want. And you did not include @.@.Error in your code, if I remember correctly Fetch is an implicit transaction per ANSI SQL. The author Dusan Petkovic got bad book title but he covered SQL Server transaction better than most writers. Hope this helps.

http://www.mhprofessional.com/product.php?cat=112&isbn=0072260939