Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query about Linked Server to Oracle Expand / Collapse
Author
Message
Posted Wednesday, November 17, 2010 11:19 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 5:06 AM
Points: 491, Visits: 555
Hi,
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
from FINCAS_PROD.LTF_UND_NOTIFICATION_NEWGEN
where app_formno=? and product=?','183248','KGB')
at OMNIUAT2FINONEUAT
)

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