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

<ammar_fake_at_vip.hr> wrote in message
news:1147371359.284586.306590_at_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
>

Is this Windows 2000 or 2003 ? You can change the properties of the DTC with the "component services" plug-in for MMC. Right-click on My Computer and then select the "MSDTC" tab.

HTH
Matthias Hoys Received on Thu May 11 2006 - 13:49:52 CDT