'MSDAORA' was unable to begin a distributed transaction - why?! (SQL <-> Oracle)

[a***@vip.hr]

Hello!

I have an Oracle linked server connected through MSDAORA. Linked server
queries work perfectly - the "openquery" ones as well as the
4-part-named ones.
The problem I have is with embedding the queries within SQL Server
triggers.

Trigger:
CREATE TRIGGER tgTest ON [dbo].[test]
FOR INSERT, UPDATE, DELETE
AS
select * from openquery(LS, 'select * from ORACLE_TEST')

executing "delete from test" in SQL Query Analyzer raises this error:

Server: Msg 7391, Level 16, State 1, Procedure tgTest, Line 5
The operation could not be performed because the OLE DB provider
'MSDAORA' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].

I've tried almost every solution I found online, but nothing helped:(
This looked promissing: http://tinyurl.com/nk2wd , but it didn't get me
any futher.

Maybe someone can get me through the troubleshoot mentioned in that
link:

- check if DTC running properly

How do I check that? If I open the "Support services" in Enterprise
Manager and right-click the "Distributed Transaction Coordinatior" I
can stop the service, what indicates the service is running, but is
there anything else I should check? I have 0 items in the right window
pane of the DTC item, is it OK?

- registry setting as discussed earlier
The following Registry Keys should be entered:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"

My entries are:

"OracleOciLib"="ociw32.dll"
"OracleSqlLib"="SQLLib18.dll"
"OracleXaLib"="xa73.dll"

Are they OK?

- check if Mtxoci.dll is loaded

There is a Mtxoci.dll in my system32 dir, but how do I tell if it's
loaded? Should I regsvr32 it?

- SET XACT_ABORT ON should be use in your SQL statement, for example:
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRAN
SELECT statement
COMMIT TRAN

I've tried that, both in trigger but also surrounding the query that
fires the trigger.

Am getting deseperate - please help. Will send candies!
TIA

[Mark D Powell]

Here is the url for the same question on the Oracle newsgroup:

http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/9a5a89b7cf2aee70/5332253cb49b350c?hl=en#5332253cb49b350c

HTH -- Mark D Powell --

[Lloyd Harrison]

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

[Taggart]

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

Post by Lloyd Harrison
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 ***

[Taggart]

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

Post by Taggart
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

Post by Lloyd Harrison
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 ***