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.