Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Query about Linked Server to Oracle Expand / Collapse
Posted Wednesday, November 17, 2010 11:19 PM


Group: General Forum Members
Last Login: Thursday, April 23, 2015 4:30 AM
Points: 494, Visits: 561
I am using SQL 2005 SP3 on Windwos 2008.
I have created Linked server to oracle. Test connection is getting successful.

When i am executing command to fetch data from Oracle server and insert it in the sql table,
insert into test
exec ('select final_decision,lcum_user_id_c,username
where app_formno=? and product=?','183248','KGB')

It throws the following error :

OLE DB provider "OraOLEDB.Oracle" for linked server "OMNIUAT2FINONEUAT" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "OMNIUAT2FINONEUAT" was unable to begin a distributed transaction.

OMNIUAT2FINONEUAT - is name of the linked server.

Whereas, when i am executing the same query to only fetch data from oracle and show in sql, at that time it is getting executed successfully.
( executing same query after removing insert into command).

I have checked properties of msdtc, nothing much help to solve the error.
Windows firewall is stopped currently.

Help please.
Post #1022633
Posted Thursday, November 18, 2010 2:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, Visits: 211
Have you tried changing Mutual Authentication Required to No Authentication Required?

I've had this issue before and got round it somehow. I'll see if I can find the code.
It might have been by using openquery instead.

Cursors never.
DTS - only when needed and never to control.
Post #1022692
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse