Transaction problems on Oracle Linked Server

Transaction problems on Oracle Linked Server

  
SYMPTOMS
When running a distributed transaction against an Oracle linked server in SQL Server 2000 (keep in mind that HubEdition queries run inside transactions...), you may receive the following error message:

Server: Msg 7391, Level 16, State 1, Line 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].

CAUSE
Microsoft Distributed Transaction Coordinator (MS DTC) was unable to perform a distributed transaction.

PLEASE CHECK
1. MS DTC Service is running (check the SQL Server Service Manager in the server taskbar)
2. The registry setting in the SQL Server machine are correct, and these dlls can be found in your \\ORACLE_HOME\bin directory.

Registry key: [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]

Oracle Registry values
Client
--------------------------------------------------------------------------

7.x "OracleXaLib"="xa73.dll"
"OracleSqlLib"="SQLLib18.dll"
"OracleOciLib"="ociw32.dll"

8.0 "OracleXaLib"="xa80.dll"
"OracleSqlLib"="sqllib80.dll"
"OracleOciLib"="oci.dll"

8.1 "OracleXaLib"="oraclient8.dll"
"OracleSqlLib"="orasql8.dll"
"OracleOciLib"="oci.dll"

9.x "OracleXaLib"="oraclient9.dll"
"OracleSqlLib"="orasql9.dll"
"OracleOciLib"="oci.dll"

For any additional information please refer to: http://support.microsoft.com/default.aspx?scid=kb;EN-US;280106

Hi.

If using OutSystems Hub Server 3.0.x there's a workaround to access data in an Oracle Linked Server through SQL Server 2000.

Let's suppose we want to execute the following query (through an Advanced Query) in an oml:

SELECT ENAME FROM OPENQUERY(DBORA01, ''select ename from SCOTT.EMP where rownum < 10'') Rowset_1;

If you type it in the advanced query editor it will generate that same "cannot start a distributed transaction" runtime error. One way to get it to work is by changing the query to:

commit transaction;
set implicit_transactions on
exec sp_executesql N'SELECT ENAME FROM OPENQUERY(DBORA01, ''select ename from SCOTT.EMP where rownum < 10'') Rowset_1; ';
set implicit_transactions off

Pay attention to the character conventions, and to the fact that every single quote character ( ' ) inside the " sp_executesql N' " block turns to ( '' - TWO SINGLE QUOTES, NOT double quote).

Also, be very careful as to where you place the Advanced Query in the action flow - as you can tell, every change made to the data prior to this query's execution will be commited - even if there's a rollback due to an exception or an explicit "Abort Transaction" action - since the query begins with a "commit transaction;" command.

Bear in mind that this is just a workaround, not a recommended practice. Use at your own risk.

Cheers,
Paulo Tavares
Hi

If using OutSystems Hub Server 3.1 here's a workaround to access data in an Oracle Linked Server through SQL Server 2000.

Let's suppose we want to execute the following query (through an Advanced Query) in an oml:

SELECT * FROM OPENQUERY(MYORACLELINKEDSERVER, 'select deptno, dname, loc from SCOTT.DEPT');

If you type it in the advanced query editor it will generate an error like The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

One way to get it to work is by changing the query to:

commit transaction;
exec sp_executesql N'SELECT * FROM OPENQUERY(MYORACLELINKEDSERVER, ''select deptno, dname, loc from SCOTT.DEPT'') ';
begin transaction;


or to

commit transaction;
exec sp_executesql N'SELECT deptno, dname, loc FROM MYORACLELINKEDSERVER..SCOTT.DEPT';
begin transaction;


You may look at the attached oml to see a working sample and to have some extra information (after publishing the eSpace, just press link "Instructions")

Regards,
Antonio
Hi

After setting up SQL server DBLink, MSDTC configurations and DLLs, I kept getting the error:

"Msg 7311, Level 16, State 2, Line 1

Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "<server>". The provider supports the interface, but returns a failure code when it is used."

Interesting enough, after changing the Provider from MSDAORA to OraOLEDB.Oracle, the error disappeared.

This happened in a DBLink connection between SQL Server 2005 and Oracle 9.2.

Cheers