Writing to Oracle Database Using Linked Server

[FrankKen]

Having difficulty writing to a linked server via a trigger. Going from SQL Server 7 to Oracle 9i. Am able to query Oracle database in Query Analyzer and insert data in Oracle table from Query Analyzer (so syntax and configuration of Linked Server appear to be fine) but get error when trying to insert data into an Oracle table via trigger. Message says OLE DB provider MSDAORA does not support distributed transactions. Any ideas welcome!

 

[mrdenny]

The problem is exactly what the error message says. The Oracle drivers do not support distributed transactions. Are you trying to run you insert/update on the SQL side within a transaction?

I would recommend feeding the insert into a temp table on the SQL Side, then feeding that data to the Oracle side with a job every few minutes. This gives you the added benifit of not having to have the Oracle server up and running for your App to work. With the trigger writting directly to the Oracle database, if the oracle database goes down, your insert/update statement on the SQL Server won't work until the server comes back up. If you queue all the inserts to the Oracle server in a table on the SQL Server, then your app won't get dinged with downtime because the oracle server is down.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[jms8222]

I agree with mrdenny and that using a temp table may be the way to go. I have run into this exact same problem but it was with SQL 2000 not SQL Server 7. So if your situation requires that you must use a trigger, then you can try the steps I took. Keep in mind, however, they may not give you the same results.

The first thing I needed to do was update the registry settings (as per Microsoft knowledge base article 280106), but I had to adjust it slightly because of Oracle 9.

These are the settings I modified on the server:
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient9.dll"
"OracleSqlLib"="orasql9.dll"
"OracleOciLib"="oci.dll"

Also you need to ensure that these DLLs are in the path as well. (I also had to have the machine rebooted before I was able to see the settings take effect.)

Second, I had to include the following line in my trigger:
SET XACT_ABORT ON

This statement specifies to SQL Server to automatically roll back the current transaction if the SQL statement raises a run-time error.

Good luck!