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