Help for distributed transaction in Oracle and SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help for distributed transaction in Oracle and SQL

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-11-01 : 01:10:59

I am trying to do a two-phase commit one in Oracle9i and the other in SQL
Server 2000.

To connect to SQL Server I have created a Heterogeneous Service(using ODBC)
and able to
query and insert records in SQL Server by creating a DB Link in Oracle
Database..

Now If i try the steps below its resulting in error
ORA-02047 cannot join the distributed transaction in progress

the step i performed are as follows.

SQL> create synonym empsyn for emp1@hsodbc; - - creating a synonym for
table on sql server

Synonym created.

SQL> insert into empsyn values ('hel'); --- inserting into table on
sqlserver.

1 row created.

SQL> insert into emp values ('hel'); -- inserting into table on local
oracle
insert into emp values ('hel')
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress

The requirement is if I insert into local Oracle table the corresponding
insert should be done
in sqlserver..

Pls let me know how to accomplish this..

Kamran Shahid
Software Engineer(MCSD.Net)
www.netprosys.com

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-01 : 23:10:55

Try from sql server with linked server to oracle?