Re: 'MSDAORA' was unable to begin a distributed transaction - why?
- From: Taggart <Taggart@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 15 Nov 2006 15:30:02 -0800
Hi 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:
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
"Lloyd Harrison" wrote:
Did you ever resolve this issue? I have had the same issue in the past
on other servers but installed an upgraded Oracle ODBC or changeing the
regedir values always work. Now I'm getting the error on a new server
and can't seem to figure out what the problem is. I have verifed
evything in the link 3 or 4 times and it is driving me crazy.
I have SQL 2005 Express installed on an XP sp2 machine with a linked
server connecting to oracle 10g. I'm able to query from the database
even update and delete as long as I do not do it through a trigger. As
soon as I try to do it through a trigger I get the Unable to begin a
distributed transaction.
Any ideas?
*** Sent via Developersdex http://www.developersdex.com ***
- References:
- Prev by Date: OK - I'm stupid....How do you create a user?
- Next by Date: Re: OK - I'm stupid....How do you create a user?
- Previous by thread: Re: 'MSDAORA' was unable to begin a distributed transaction - why?
- Next by thread: Re: SQL Server 2005 Database Security
- Index(es):
Relevant Pages
|
