Re: SQL Port: using of the ROLLBACK
- From: "Leonid Ganeline" <leo_gan_57@xxxxxxxxxxx>
- Date: Tue, 5 Dec 2006 10:57:20 -0800
Hi Matt,
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" <MattMeleski@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C226E5DC-740B-4612-8802-365B056805E5@xxxxxxxxxxxxxxxx
Hi Leo,.
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" wrote:
Hi,
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
================================
- References:
- SQL Port: using of the ROLLBACK
- From: Leo Gan
- RE: SQL Port: using of the ROLLBACK
- From: Matt Meleski
- SQL Port: using of the ROLLBACK
- Prev by Date: Re: Interchange pipeline failure
- Next by Date: RE: BizUnit
- Previous by thread: Re: SQL Port: using of the ROLLBACK
- Next by thread: Re: SQL Port: using of the ROLLBACK
- Index(es):
Relevant Pages
|
