RE: SQL Port: using of the ROLLBACK

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
================================


.

Relevant Pages

  • RE: Problem sending transactional Msmq messages with 2004 adapter
    ... The MSMQ adapter, also known as MSMQ/C adapter, allows ... non-transactional, public and private, and local and remote queues. ... Creates a new batch enlisting in the same original transaction and moves ... original transaction; otherwise, it rolls back the messages to the queues. ...
    (microsoft.public.biztalk.server)
  • Re: Help, SQL adapter deadlocked!!!
    ... The SQL port always creates the transaction across the called stored ... adapter is Serializable. ...
    (microsoft.public.biztalk.general)
  • Re: TableAdapters and Transactions again!
    ... every adapter, the following code is a generic method that works on any ... SqlTransaction transaction) ... foreach (SqlCommand command in commands) ... always null after I created the TableAdapter. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: SQLCE, DataAdaptors, MultipleTables, Updates and Inserts
    ... >> Do one update and then the other, within a transaction if you want to. ... >> connection from two different threads at the same time. ... > Set Insert/Update commands on adapter for table1 ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: SQL Port: using of the ROLLBACK
    ... As you stated the adapter is trying to Commit ... or Rollback a Transaction with @@Trancount = 0, ... If you open up the Component Services SnapIn on the BizTalk ... Event Category: BizTalk Server 2004 ...
    (microsoft.public.biztalk.general)