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

From: <ammar_fake_at_vip.hr>
Date: 11 May 2006 11:15:59 -0700
Message-ID: <1147371359.284586.306590@i39g2000cwa.googlegroups.com>

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 Received on Thu May 11 2006 - 13:15:59 CDT