'MSDAORA' was unable to begin a distributed transaction - why?

'MSDAORA' was unable to begin a distributed transaction - why?

Post by VGFnZ2Fyd » Wed, 15 Nov 2006 14:57:02


Hi Lloyd,

I'm in the same boat except I can't even get as far as the OLEDB provider to
accept the BEGIN TRANSACTION statement let alone an insert/update via a
trigger!

I can query OK through OPENQUERY but soon as I try running this SQL in query
analyser as a test:-

"USE ROAMReports;


BEGIN TRANSACTION

UPDATE debit_transactions
SET in_gate = 'Y',
gate_date_charge = rs.datecharged
FROM OPENQUERY (GATE_PR,
'
SELECT a.accountidentifier, c.reason, c.amount, c.datecharged
FROM ctcs_tagaccount a
INNER JOIN ctcs_charge c ON a.tagaccountguid = c.chargeagainsttagaccountguid
INNER JOIN ctcs_tollproduct tp ON a.tollproductguid = tp.tollproductguid
WHERE c.reason = ''Reversal Of Free Toll Credits June''
OR c.reason = ''Reversal Of Free Toll Credits July''
') AS rs, debit_transactions dt
WHERE dt.additional_text = rs.reason
AND dt.account_id = rs.accountidentifier
AND dt.adjust_amt = rs.amount
AND dt.load_to_aces = 'L'
AND dt.in_clarify = 'Y'
AND dt.in_gate = 'N'
AND debit_id = dt.debit_id


ROLLBACK TRANSACTION"

I get this:-
Server: Msg 7391, Level 16, State 1, Line 6
The operation could not be performed because the OLE DB provider
'OraOLEDB.Oracle' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle'
ITransactionJoin::JoinTransaction returned 0x8004d00a].


The linked server is set up with RPC and RPC OUT toggled on and as far as I
can tell all the registry entries are in place (although they use Oracle 8.1
DLL's and the target is 10G - which I know is a bit weird and may even be the
problem but was set up before I arrived on site). MSDTC and all it's
dependencies are up and running too.

I'm stuck. Do you know whether Oracle has the capability to block RPC calls
at it's end? Are you able to confirm your registry versions so I can x-ref to
my settings?

Slainte,
Glyn
 
 
 

'MSDAORA' was unable to begin a distributed transaction - why?

Post by VGFnZ2Fyd » Fri, 17 Nov 2006 08:30:02

i LLoyd,

With the help of one of the DBA's I have managed to get this working from a
SQLServer2003 box using SQLServer 2000 to an Oracle 10G database by using the
settings described for Oracle Client 8.1 in article
http://support.microsoft.com/kb/280106

We have given up on getting it to work on the XP box because even though all
the registry settings, firewall settings and linked server settings and
username are identical it just steadfastly refuses to work. The only diff is
the Windows O/S. My conclusion, being a Unix guy, is that XP presumably
stands for Xtremely Poor Operating System?

Slainte,
Glyn

"Taggart" wrote: