CODEWITHSUNDEEP
sql-serveroracle10gdistributedlinked-server
Jonathan Escobedo
Jonathan Escobedo

Reputation: 4053

Execute a distributed transaction for an Oracle linked server using SQL Server 2000

I'm trying to execute a distributed transaction for a single Oracle linked server using SQL Server 2000, I came up with the following script:

BEGIN DISTRIBUTED TRANSACTION;
  SET XACT_ABORT  off;
  GO

  SELECT MAX(DEPTNO)+1,
  FROM [WSF08_CONTA_ORADATA_II]..[SCOTT].DEPT

  SET XACT_ABORT  on;
  GO

COMMIT TRANSACTION;

As soon as the script is ran I get the following errors followed by SQL Management studio freezing and closing the connection.

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "OraOLEDB.Oracle" for linked server "WSF08_CONTA_ORADATA_II" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7303, Level 16, State 1, Line 3 Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "WSF08_CONTA_ORADATA_II".

What is going on?

*P.D:

  1. I can create/update/delete data using regular queries but error shows up when the DISTRIBUTED TRANSACTION clause is involved.
  2. We recently upgraded or linked servers to Oracle 10g, It is worth to mention that I did not run into issues we were using 9i.

Upvotes: 1

Views: 1541

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294287

To enroll SQL Server and Oracle in a distributed transaction MSDTC needs to have XA transactions enabled. See Supporting XA Transactions:

When the DTC acts as an XA-compliant transaction manager, Oracle, IBM DB/2, Sybase, Informix, and other XA-compliant resource managers can participate in transactions that the DTC controls.

Upvotes: 3

Related Questions