ROLLBACK in the stored procedures started by BizTAlk SQL port

ROLLBACKs create the interesting events.

 

I’ve spent a time trying to understand them.

 

When I’ve tried to use

It was interesting as this SP has done ROLLBACK if I test it without BizTalk.

I’ve got the description of this situation in the BizTalk 2006 Help:
“Save Tran and RollBack tran in stored procedures will cause an error event from the SQL adapter, but the transaction does roll back.”

 

Matt Meleski gave me the decision:

 

“…A simple trick that you could try would be to test for an exisitance of a
transaction before starting a new one in the proc. But this would require
minor modifications to your stored procedure. For example:

 

At the top of the proc,

 

Declare @MyTran bit

 

— If Caller Started the Transaction, let the caller handle it
— otherwise control it in the proc.
If @@Trancount = 0
Begin
    Begin Transaction
    Set @MyTran = 1
End

 

At the bottom of your proc, to rollback or commit:

 

If @MyTran = 1 and @@Trancount > 0
 Begin
          Rollback Transaction — Or Commit Transaction
 End

 

…”

 

It works!!!

 

Thanks Matt!

 

———————————————————–
Event Type: Warning
Event Source: BizTalk Server 2004
Event Category: BizTalk Server 2004
Event ID: 5740
Date: 11/28/2006
Time: 4:21:07 PM
User: N/A
Computer: CORPBTS1
Description:
The adapter “SQL” raised an error message. Details “HRESULT=”0x80004005”
Description=”Cannot roll back MAIN. No transaction or savepoint of that name
was found.”
“.

 

 

AND then the second event

 

 

 

———————————————————–

When I change the

ROLLBACK TRAN MAIN

to

ROLLBACK WORK

the event changed to:

———————————————————–

 

 

 

AND then the second event

 

 

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
———————————————————–