Linked server error 1

[jms8222]

I've seen this error posted in the archives, but none of the solutions I've read have helped me thus far.

I have created a linked server in SQL 2000 to Oracle. I can successfully execute a SELECT and UPDATE from Query Analyzer, however, if I put the UPDATE statement into a SQL trigger, I get the following error message:

Server: Msg 7391, Level 16, State 1
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 updating the sqlnet.ora file. I've also tried updating the registry per Microsoft article 280106 with no luck.

Any suggestions would be GREATLY appreciated!!!

 

[johnherman]

Is DTC (Distributed Transaction Coordinator) running?

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[jms8222]

Yes it is. Stopping it causes this error:
MSDTC on server 'myServerName' is unavailable.

 

[johnherman]

Hmmm. Seems to be something related to security at the trigger or target database. You say it runs in QA; what login? Is the trigger using the same security? MSDAORA security? Can you do a SELECT from the trigger?

Just trying to narrow down the possibilities...

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[jms8222]

I have FINALLY been able to find the solution to this error!!

In the end, it turns out that the solution was right before me all along (DOH!)

First, I needed to update the registry settings (as per Microsoft knowledge base article 280106), but I had to adjust it slightly because we are using Oracle 9!

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

(I also had the machine rebooted before I was able to see the settings take effect. Though now, as an afterthought, I wonder if just stopping and starting the DTS would have done the trick. Oh well.)

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.

I can only hope that my posting of this solution can help someone out there avoid all the trials I've gone through with this issue!!! Thanks to all that provided feedback!

 

[toekneel]

Thanks for your foresight in thinking someone else might have this issue!

In my environment, I had to also set the following:

SET ANSI_NULL_DFLT_ON ON
SET ANSI_WARNINGS ON

Again, THANKS!!!!