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
 
BEGIN TRAN MAIN
...
ROLLBACK TRAN MAIN
 
inside of the SP, that is started by BizTAlk SQL port,
And I've got couple events: (they are bellow)
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!
 
As I can understand the SQL Port create the wrapping transaction and...
in result I cannot properly use the  ROLLBACK feature.
 
No problem. But the text of the events could be more accurate, doesn't it?
========================================================================
There are the text of those events:
-----------------------------------------------------------
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."
".
 
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
 
AND then the second event
 
Event Type: Warning
Event Source: BizTalk Server 2004
Event Category: BizTalk Server 2004
Event ID: 5743
Date: 11/28/2006
Time: 4:21:07 PM
User: N/A
Computer: CORPBTS1
Description:
The adapter failed to transmit message going to send port
"SQL://CORPNT5/Prod/". It will be retransmitted after the retry interval
specified for this Send Port. Details:"HRESULT="0x80004005"
Description="Cannot roll back MAIN. No transaction or savepoint of that name
was found."
".
 
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
 
-----------------------------------------------------------
When I change the
ROLLBACK TRAN MAIN
to
ROLLBACK WORK
the event changed to:
-----------------------------------------------------------
 
Event Type: Warning
Event Source: BizTalk Server 2004
Event Category: BizTalk Server 2004
Event ID: 5740
Date: 11/28/2006
Time: 4:16:09 PM
User: N/A
Computer: CORPBTS1
Description:
The adapter "SQL" raised an error message. Details "HRESULT="0x80040e14"
Description="Distributed transaction completed. Either enlist this session
in a new transaction or the NULL transaction."
".
 
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
 
AND then the second event
 
Event Type: Warning
Event Source: BizTalk Server 2004
Event Category: BizTalk Server 2004
Event ID: 5743
Date: 11/28/2006
Time: 4:16:09 PM
User: N/A
Computer: CORPBTS1
Description:
The adapter failed to transmit message going to send port
"SQL://CORPNT5/Prod/". It will be retransmitted after the retry interval
specified for this Send Port. Details:"HRESULT="0x80040e14"
Description="Distributed transaction completed. Either enlist this session
in a new transaction or the NULL transaction."
".
 
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
-----------------------------------------------------------
Print | posted on Wednesday, November 29, 2006 11:53 AM

Feedback

# re: ROLLBACK in the stored procedures started by BizTAlk SQL port

left by Guess at 12/20/2006 10:05 AM Gravatar
Hi,

I got exactly the same error and I tried the same trick but It didn't work. For some reason, when I try the code below, all statements inside the if statement never get executed. My @@Trancount is always greater than 0.
If @@Trancount = 0
Begin
Begin Transaction
Set @MyTran = 1
--Insert/Update statement here
End

Have you ever experienced this? Any suggestion of what to do?

Thanks.

# re: ROLLBACK in the stored procedures started by BizTAlk SQL port

left by AlanG at 4/1/2008 1:06 PM Gravatar
Your Insert/Update statement should be outside the begin/end block. That block should only begin the transaction. Have look at the original blog above and put your code where Leonid has the line 'At the bottom of your proc, to rollback or commit:'.

# re: ROLLBACK in the stored procedures started by BizTAlk SQL port

left by ASP Web Hosting at 11/3/2008 7:59 AM Gravatar
Hmmmm.... im still struggling with rolling back named transactions...

# re: ROLLBACK in the stored procedures started by BizTAlk SQL port

left by Albert at 6/9/2010 8:00 AM Gravatar
Events are not appearing anymore, but rollback feature is not working. Do you know why? Thanks

# re: ROLLBACK in the stored procedures started by BizTAlk SQL port

left by steve at 9/14/2011 3:47 PM Gravatar
I have a SP being called from a SQL port and although if I run the query captured from SQL Profiler it works a charm, it keeps getting rolled back. I have an autoincrement and can see it retrying but it rolls it back each time, I don't understand why. When I run it directly, it just works.

Steve.
Post A Comment
Title:
Name:
Email:
Comment:
Verification: