Home » RDBMS Server » Networking and Gateways » Using SQL Server resultset in Oracle procedure (Oracle 10g)
Using SQL Server resultset in Oracle procedure [message #328594] Fri, 20 June 2008 10:48 Go to next message
white_eagle
Messages: 3
Registered: June 2008
Junior Member
I get the error message-'ORA-02047: cannot join the distributed transaction in progress' when trying to update an Oracle database table while looping through data returned by a SQL Server procedure.

My inisql.ora file includes the following lines:
HS_FDS_IS_FUNC=TRUE
HS_FDS_RESULT_SUPPORT=TRUE
HS_FDS_TRANSACTION_MODEL=READ_ONLY

My Oracle procedure includes the following, and fails at the piont where attempting to insert into the Oracle table PTL_Test

curMSQL SYS_REFCURSOR;
rec PTL_TASKLIST_IMPORT%rowtype;
sEmail varchar(100) := 'testname@test.com';
BEGIN
"dbo"."spCHPGetPortalData"@BETH.AMS(sEmail, 4, curMSQL);
for i in 1 .. 1
LOOP
FETCH curMSQL INTO rec;
EXIT WHEN curMSQL%NOTFOUND;

INSERT INTO PTL_Test
SELECT 4 FROM DUAL;
END LOOP;
COMMIT;
CLOSE curMSQL;

COMMIT;


Eventually I want to update an Oracle table from the data retrieved from the SQL Server procedure.

As far as I can see I have everything set up correctly, so what could be the cause of the error?

The DB Link works and if I just display the data retrieved from the Stored proc without attempting to update the Oracle table, everything is OK. so why does the error occur?

Incidentally when I examine v$lock, I have no locks in place.
Re: Using SQL Server resultset in Oracle procedure [message #328595 is a reply to message #328594] Fri, 20 June 2008 10:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
02047, 00000, "cannot join the distributed transaction in progress"
// *Cause: Either a transaction is in progress against a remote database
//         that does not fully support two phase commit, and an update
//         is attempted on another database, or updates are pending and
//         and an attempt is made to update a different database that
//         does not fully support two phase commit.
// *Action: complete the current transaction and then resubmit the
//          update request.


>As far as I can see I have everything set up correctly
Obviously Oracle disagrees with you.

1) I suggest eliminating the COMMIT from inside the cursor loop.
2) INSERT INTO PTL_Test values(4) -- is more efficient

[Updated on: Fri, 20 June 2008 10:57] by Moderator

Report message to a moderator

Re: Using SQL Server resultset in Oracle procedure [message #328596 is a reply to message #328595] Fri, 20 June 2008 11:01 Go to previous messageGo to next message
white_eagle
Messages: 3
Registered: June 2008
Junior Member
Yes, I have read the manual, but this does not explain why the error is occurring in my situation.
There is no distributed transaction in progress as far as I can see, the linked SQL Server is read only. SO I have executed the stored procedure on the SQL Server, read the first record from the returned resultset and at the only attempt to update a table the error occurs.
Re: Using SQL Server resultset in Oracle procedure [message #328599 is a reply to message #328594] Fri, 20 June 2008 11:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Using SQL Server resultset in Oracle procedure [message #329676 is a reply to message #328594] Thu, 26 June 2008 03:40 Go to previous messageGo to next message
white_eagle
Messages: 3
Registered: June 2008
Junior Member

The solution was to remove the HS_FDS_TRANSACTION_MODEL=READ_ONLY
from the inisql.ora file.

Re: Using SQL Server resultset in Oracle procedure [message #329775 is a reply to message #329676] Thu, 26 June 2008 08:40 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
white_eagle wrote on Thu, 26 June 2008 01:40

The solution was to remove the HS_FDS_TRANSACTION_MODEL=READ_ONLY
from the inisql.ora file.



As far as I know, neither (HS_FDS_TRANSACTION_MODEL or inisql.ora) is from generic, out of the box RDBMS Oracle.

So how did they get onto your system & in your way?
Previous Topic: OpenLDAP ldapsearch connect to OID with SSL?
Next Topic: why is PLSExtProc used in listener file
Goto Forum:
  


Current Time: Thu Mar 28 20:38:26 CDT 2024