|
Home > Archive > BizTalk Server Orchestration > December 2005 > SQL Adapter and Stored Procedure ROLLBACK TRANSACTION Statements
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Adapter and Stored Procedure ROLLBACK TRANSACTION Statements
|
|
| microsoft.public.biztalk.orchestration 2005-12-08, 5:54 pm |
| I have a question about SQL Transactions in stored procedures called by the
BizTalk SQL Adapter:
If a stored procedure contains a Transaction block and ROLLBACK TRAN is
called, this seems to unavoidably result in a SqlXmlException. Two errors
can occur:
1. If any SQL statement follows the ROLLBACK TRAN then the error description
says:
HRESULT="0x80040e14" Description="Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction."
2. If no SQL Statement follows the ROLLBACK TRAN then the error description
says:
HRESULT="0x80004005" Description="Transaction count after EXECUTE indicates
that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count
= 1, current count = 0."
Would it be fair to say that the BizTalk SQL Adapter "shares" its
Transaction with the Transaction in Stored Procedures, such that if the
Stored Procedure calls a ROLLBACK then this affects the Transaction in the
SQL Adapter? I've heard the SQL Adapter described as "non transactional"
and that if you want to utilize ROLLBACK statements in Stored Procedures
then you seem to have two options:
1.. You continue to use the SQL Adapter and catch one of the above
exceptions and then perform a Compensation in your Orchestration if
necessary
2.. You do away with the SQL Adapter and roll your own .NET Serviced
Component to perform the Stored Procedure calls
COMMIT Transaction does not suffer the same fate. No exceptions are thrown
when the SP calls its own COMMIT TRAN.
Is this an accurate picture of the SQL Adapter in BTS 2004 SP1 and BTS 2006?
Can anyone confirm my conclusions or point me to a more detailed discussion
of this matter?
Many Thanks,
Daniel Hester
danielh@magenic.com
| |
| Daniel H 2005-12-14, 5:54 pm |
| I'm still plugging away on TRANSACTION rollbacks in Stored Procedures called
by BizTalk's SQL Adapter.
Latest update -- Naming the nested Transaction doesn't work. L This seems
due to the fact that only the first (outermost) transaction name is
registered with the system and the BizTalk Adapter creates and owns this
one. The error you get is:
"Cannot roll back <MyTransactionName>. No transaction or savepoint of that
name was found."
From the SQL Books Online entry on BEGIN TRANSACTION:
"Naming multiple transactions in a series of nested transactions with a
transaction name has little effect on the transaction. Only the first
(outermost) transaction name is registered with the system. A rollback to
any other name (other than a valid savepoint name) generates an error. None
of the statements executed before the rollback are in fact rolled back at
the time this error occurs. The statements are rolled back only when the
outer transaction is rolled back."
(I tried using SAVE TRAN to identify my nested Transaction, but SAVE
TRANSACTION doesn't work within Distributed Transactions.)
Inspecting @@TRANCOUNT before either beginning or rolling back a Transaction
in the SP does not work either. L
In my SP I had:
IF @@TRANCOUNT = 0
BEGIN TRAN
And before rolling back I had:
IF @@TRANCOUNT > 0
ROLLBACK TRAN
With the above code in my SP, the BizTalk SQL Adapter complains that:
"Transaction Count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count = 0."
My conclusions are as follows:
a.. It seems like you can increment the number of Transactions in the
execution environment with BEGIN TRAN or BEGIN TRAN @TranName in your SP.
b.. However you can't target these nested Transactions for Rollback
because:
a.. The system cannot find any named Transactions because they're nested
and therefore not registered
b.. Calling ROLLBACK TRANSACTION in your SP always targets the BizTalk
SQL Adapter's Outer Transaction and it doesn't like that.
My recommendation appears to be:
a.. You *have to* remove your TRANSACTION blocks in your SQL SPs called by
BizTalk and just RAISERRORs (with an Error Level > 10 if you intend to catch
the error as a SOAP Exception in your Orchestration). This amounts to
putting your faith in the BizTalk Adapter's ability to detect an error and
rollback it's Transaction.
Can anyone suggest any other avenues of approach that I haven't considered?
Many Thanks,
Daniel
danielh@magenic.com
"microsoft.public.biztalk.orchestration" <danielh@magenic.com> wrote in
message news:uY0LMOD$FHA.3872@TK2MSFTNGP12.phx.gbl...
>I have a question about SQL Transactions in stored procedures called by the
>BizTalk SQL Adapter:
>
>
>
> If a stored procedure contains a Transaction block and ROLLBACK TRAN is
> called, this seems to unavoidably result in a SqlXmlException. Two errors
> can occur:
>
>
>
> 1. If any SQL statement follows the ROLLBACK TRAN then the error
> description says:
>
>
>
> HRESULT="0x80040e14" Description="Distributed transaction completed.
> Either enlist this session in a new transaction or the NULL transaction."
>
>
>
> 2. If no SQL Statement follows the ROLLBACK TRAN then the error
> description says:
>
>
>
> HRESULT="0x80004005" Description="Transaction count after EXECUTE
> indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
> Previous count = 1, current count = 0."
>
>
>
> Would it be fair to say that the BizTalk SQL Adapter "shares" its
> Transaction with the Transaction in Stored Procedures, such that if the
> Stored Procedure calls a ROLLBACK then this affects the Transaction in the
> SQL Adapter? I've heard the SQL Adapter described as "non transactional"
> and that if you want to utilize ROLLBACK statements in Stored Procedures
> then you seem to have two options:
>
>
>
> 1.. You continue to use the SQL Adapter and catch one of the above
> exceptions and then perform a Compensation in your Orchestration if
> necessary
> 2.. You do away with the SQL Adapter and roll your own .NET Serviced
> Component to perform the Stored Procedure calls
>
>
> COMMIT Transaction does not suffer the same fate. No exceptions are
> thrown when the SP calls its own COMMIT TRAN.
>
>
>
> Is this an accurate picture of the SQL Adapter in BTS 2004 SP1 and BTS
> 2006? Can anyone confirm my conclusions or point me to a more detailed
> discussion of this matter?
>
>
>
> Many Thanks,
>
>
>
>
>
> Daniel Hester
>
> danielh@magenic.com
>
>
|
|
|
|
|