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

[ammar...@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

[Matthias Hoys]

<ammar...@vip.hr> wrote in message
news:1147371359.2...@i39g2000cwa.googlegroups.com...

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

[ammar...@vip.hr]

It's the 2000 Server one.

[Mark D Powell]

What version of the Oracle client is installed on the SQL Server box?
I ask because with vesion 8.1 I believe that the Oracle OLE support
routines were installed automatically as part of a normal Oracle client
install, but with my 9.2 client install I had to run a second custom
install to get the OLE piece. I did not have to configure any of the
Windows registry entries manually. I just did the two part Oracle
client install and created the link database and our queries were
working. With the 8.1 version I only had to do the Oracle client
install to get our linked queries to work. I cannot say if the MS
developers use the linked database from within table triggers.

HTH -- Mark D Powell --

[ammar...@vip.hr]

Hi!
I've got Oracle ODBC Driver ver. 8.00.06.00 (dated back in 1999) and MS
ODBC for Oracle ver 2.573.7926.00 .
I built my linked server with the MS Version.
Was it a mistake? :)

[Mark D Powell]

I would want my Oracle client version on the SQL Server box to match
the target Oracle database version if possible. The Oracle clients are
free and available for download off technet: http://otn.oracle.com.

Does the Oracle username (ID) being used in the linked database
definition have the delete object privilege for the table in question?
And whose security context does the linked database run in?

[ammar...@vip.hr]

Hi Mark!
Actually, I'm not quite sure what driver am I useing for my linked
server. It just says "Microsoft OLE DB Provider for Oracle", so I
guessed it's the same driver mentioned in the ODBC portion of the
system settings. What would be the best way to find out that driver and
version the linked server is using?

Unfortunatelly, I can't replace the driver that easily as I'm not the
administrator of the server involved.

I'm performing read-only operations in Oracle only, so as far as I'm
concerned, I need no transactions at all (at least for the trigger
segment), but the answer your question - yes, I do have privileges to
update the records from the Oracle table, I guess I also have the
delete permissions but am afraid to try it out since it's the
procudtion enviroment on the Oracle side.
How do I find ou tin whose security context the linked server runs in?

[Joel Garry]

>I'm performing read-only operations in Oracle only, so as far as I'm concerned, I need no transactions at all

Oracle's concerns are perhaps more relevant :-) Oracle needs to know
when your transaction begins so as to know what read-consistent view of
the data to give you.

"A transaction in Oracle begins when the first executable SQL statement
is encountered. An executable SQL statement is a SQL statement that
generates calls to an instance, including DML and DDL statements."

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14220/transact.htm#sthref624

I'm not quite sure I follow why deleting would be a read-only
operation. I don't understand non-Oracle trigger syntax.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20060512/news_1b12google.html

[ammar...@vip.hr]

I probably have permission to delete the Oracle records, but I don't
actually perform the delete operations, hence no need for Oracle
transactions as I don't change anything on the Oracle side.
But from technical point of view, I can understand it everything gets
pushed through transactions, I was thinking maybe I could tell the SQL
Server not to tunnel the Oracle queries through such transactions.
I'll try to check the version and publisher of the Oracle driver
backing the linked server.