SQL Port: using of the ROLLBACK
Leo Gan
When I tried to use
BEGIN TRAN MAIN
...
ROLLBACK TRAN MAIN
inside of the SP which is started by BizTAlk SQL port,
I've got couple 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've changed to the:
BEGIN TRAN MAIN
...
ROLLBACK WORK
I've got the different events
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.
As I can understand the SQL Port create the wrapping transaction and...
in result I cannot properly use the ROLLBACK feature.
Or there is something different?
Have any ideas??
Thanks for help!
Leonid Ganeline
BizTalk Solution Developer
===========================
http://geekswithblogs.com/leonidganeline
================================
Matt Meleski
You can simulate by opening a new query connected to any database using
Query Analyzer or the Sql 2005 Management Studio.
With a new query window open execute the following statement:
begin tran test
print str(@@trancount)
begin tran
print str(@@trancount)
rollback tran test
print str(@@trancount)
You will get :
1
2
0
The final rollback will drop the trancount = 0
But if you do the following:
begin tran test
print str(@@trancount)
begin tran
print str(@@trancount)
commit tran test
print str(@@trancount)
Interestingly enough you will get:
1
2
1
The only way is to call a rollback is how I described a couple of posts
back, but I realize that may not fit your needs.
Matt
"Leo Gan" wrote:
> Is that mean I cannot use ROLLBACK in the stored procedures (which are
> called from SQL port) at all?
>
> --
> Regards
>
> Leonid Ganeline
> BizTalk Solution Developer
> ===========================
> http://geekswithblogs.com/leonidganeline
> ================================
>
>
> "Leo Gan" <leo_g...@hotmail.com> wrote in message
> news:DAEDE648-8225-4FFF...@microsoft.com...
Matt Meleski
A feature on the Sql Adapter that I have wished for, is a way to turn off
the the automatic transactions. As you stated the adapter is trying to Commit
or Rollback a Transaction with @@Trancount = 0, with the error being
produced.. If you open up the Component Services SnapIn on the BizTalk
machine and navigate to the Microsoft.BizTalk.Adapter.SQL and look at the
properties for each component, each of the components Transactions properties
are set to -> Required.
I have never tried this and would not recommend it anyways as it might have
an adversely affect other processes that are using these compoenets, but you
could try as an experiment setting the Transaction Property to Disabled for
each component, so you could start your own transaction in the Stored
Procedure, without receiving your errors.
Is this a matter of calling an existing stored procedure that other client
apps are calling that need the transaction in the proc.?
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 @TranStartedIndicator bit
-- If Caller Started the Transaction, let the caller handle it
-- otherwise control it in the proc.
If @@Trancount = 0
Begin
Begin Transaction
Set @TranStartedIndicator = 1
End
At the bottom of your proc, to rollback or commit:
If @TranStartedIndicator = 1 and @@Trancount > 0
Begin
Rollback Transaction -- Or Commit Transaction
End
The only other things I can suggest is to use .Net Code to call the proc,
or use another adapter such as Enabler Groups, which allows you to set a
property on your Receive Location or Send Port to disable automatic
transaction enabling.
Matt
Leo Gan
called from SQL port) at all?
--
Regards
Leonid Ganeline
BizTalk Solution Developer
===========================
http://geekswithblogs.com/leonidganeline
================================
"Leo Gan" <leo_g...@hotmail.com> wrote in message
news:DAEDE648-8225-4FFF...@microsoft.com...
Leonid Ganeline
--
Regards
Leonid Ganeline
BizTalk Solution Developer
===========================
http://geekswithblogs.com/leonidganeline
================================
"Matt Meleski" <MattM...@discussions.microsoft.com> wrote in message
news:C226E5DC-740B-4612...@microsoft.com...
Leonid Ganeline
It works fine! Thanks.
Can I place your advise in my blog with reference to you?
--
Regards
Leonid Ganeline
BizTalk Solution Developer
===========================
http://geekswithblogs.com/leonidganeline
================================
"Matt Meleski" <MattM...@discussions.microsoft.com> wrote in message
news:C226E5DC-740B-4612...@microsoft.com...
Leonid Ganeline
No way to work with ROLLBACK in stored procedures when we start them from
the SQL ports?
Matt Meleski give me a decision of this problem.
Thanks Matt!
http://geekswithblogs.net/leonidganeline/archive/2006/11/29/99485.aspx