Re: SQL Port: using of the ROLLBACK

Tech-Archive recommends: Fix windows errors by optimizing your registry

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_gan_57@xxxxxxxxxxx> wrote in message news:DAEDE648-8225-4FFF-BDF9-645D6D5338E7@xxxxxxxxxxxxxxxx
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: 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)
  • Re: SQL Port: using of the ROLLBACK
    ... or Rollback a Transaction with @@Trancount = 0, ... If you open up the Component Services SnapIn on the BizTalk ... transaction before starting a new one in the proc. ... Event Category: BizTalk Server 2004 ...
    (microsoft.public.biztalk.general)
  • Re: SQL Adapter or call SQL from C# Helper class
    ... Here I mention only Send Port functionality. ... And ADO.NET client of SQL is more flexible, more manageable then "BizTalk" ... The SQL Send Port + Orchestration are clumsy. ...
    (microsoft.public.biztalk.general)
  • RE: Strange problems with parties
    ... Reporting, then Covast EDI reports ... > I'm using biztalk 2004 with covast accerator ... The send port is filtered to look at the receive port, ... > matching subscription for the message. ...
    (microsoft.public.biztalk.general)
  • Re: Using "Oracle" adapter
    ... I was about to ask you this question in my previous post "Do you have BizTalk ... try setting it to your BTS server management database. ... Even though it asks to select port, you can still click next and give the ...
    (microsoft.public.biztalk.general)