Oracle Distributed Transactions

I am attempting to update an oracle table from a insert trigger on my SQL table. When using a trigger to run a sql command against the ole db oracle client (linked server) a distributed transaction is initiated and i get a message stating. "The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."

I can update the oracle table through a query however just not throught a distributed transaction.

However according to all the documentation i can find it states that I should be able to perform this.

Can anyone shed any insight?

I'm not sure, but here's a suggestion. I've had this issue with SQL's ole provider, and it turned out to be RPC was blocked at the core, but that was SQL to SQL not MSDAORA.

1) Make sure that MSDTC is the same version on both servers. Distributed transactions (for SQL server anyway) run through the MSDTC.

2) Make sure WINS resolution works between servers (ping the names). MSDTC uses WINS name resolution.

3) Make sure RPC works or ports are accessible from / to both machines (try mapping a drive). MSDTC is DCOM and requires RPC for the connection and the distributed transaction - the communication can be specified for a particular port range in the registry, but the initiation of DTC is over RPC ports.


------------
David Bobko at 11/5/01 2:38:12 PM

I am attempting to update an oracle table from a insert trigger on my SQL table. When using a trigger to run a sql command against the ole db oracle client (linked server) a distributed transaction is initiated and i get a message stating. "The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."

I can update the oracle table through a query however just not throught a distributed transaction.

However according to all the documentation i can find it states that I should be able to perform this.

Can anyone shed any insight?

Mapping a drive from a SQl / Win 2000 machine to an ORacle / Unix machine?


------------
Gordon Klundt at 11/6/01 4:06:14 PM

I'm not sure, but here's a suggestion. I've had this issue with SQL's ole provider, and it turned out to be RPC was blocked at the core, but that was SQL to SQL not MSDAORA.

1) Make sure that MSDTC is the same version on both servers. Distributed transactions (for SQL server anyway) run through the MSDTC.

2) Make sure WINS resolution works between servers (ping the names). MSDTC uses WINS name resolution.

3) Make sure RPC works or ports are accessible from / to both machines (try mapping a drive). MSDTC is DCOM and requires RPC for the connection and the distributed transaction - the communication can be specified for a particular port range in the registry, but the initiation of DTC is over RPC ports.


------------
David Bobko at 11/5/01 2:38:12 PM

I am attempting to update an oracle table from a insert trigger on my SQL table. When using a trigger to run a sql command against the ole db oracle client (linked server) a distributed transaction is initiated and i get a message stating. "The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction."

I can update the oracle table through a query however just not throught a distributed transaction.

However according to all the documentation i can find it states that I should be able to perform this.

Can anyone shed any insight?

Did you get an anwer?

I have same issue.

e think it could be Oracle HS (heterogeneous services) Gateway configuration problem.

I can do the insert from a trigger from sql server 2005 to sql server 2005, but not from sql server to Oracle 10g.

You do not need to use Oracle HS for connecting from SQL Server to Oracle, it is for other way around. Oracle HS supports query only, to do INSERT/UPDATE you need to use transparent gateway which is separately licensed product.

Try to use MS Driver for Oracle in Linked Server instead of Oracle driver.