Query about Linked Server to Oracle

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Query about Linked Server to Oracle

Query about Linked Server to Oracle

Rate Topic

Display Mode

Topic Options

Author

Message

chetanr.jain

Posted Wednesday, November 17, 2010 11:19 PM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:13 AM
Points: 465, Visits: 437

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

nigelrivett

Posted Thursday, November 18, 2010 2:20 AM

Old Hand

Group: General Forum Members
Last Login: Monday, January 28, 2013 1:45 AM
Points: 386, Visits: 199

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 »

Permissions