SQL Server Performance Forum – Threads Archive

Linked Server to Oracle 9.2

I have a linked server connection established between my SQL Server 2005 database (loaded on my Desktop XP PRO with SP2) and my Oracle 9.2 Development Database (UNIX Box). I’m using the OraOLEDB.Oracle provider for my Linked Server connection. The connection is setup and I can run SELECT, UPDATE, INSERT, DELETE statement from my Query Analyzer screen and in Stored Procedures against my Oracle 9.2 database and they all run successfully. However, what I really need is to be able to run those UPDATE, INSERT and DELETE statements inside of a trigger on my SQL Server database. When I place those same statement inside my Trigger, I get the following error message: OLE DB provider "OraOLEDB.Oracle" for linked server "ORADB2" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure u_PDA_STAGE_NGSUSERS, Line 12
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORADB2" was unable to begin a distributed transaction. Does ANYONE know of anyway to get this issue resolved? I’ve looked over several documents, web blogs, white papers, etc. to find a solution the past 2 days and have been unsuccessfull, any help would be greatly appreciated?

Refer this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;280106 though it refers for SQL 2000, ensure steps are followed thorougly.
Another KBA suggests:
Contact the vendor of the driver you use in your linked server query to see whether or not the driver supports distributed transactions. Verify that your network name resolution works. Verify that the servers can communicate with one another by name, not just by IP address. Check in both directions (for example, from server A to server B and from server B to server A). You must resolve all name resolution problems on the network before you run your distributed query. Start the Distributed Transaction Coordinator (DTC or MSDTC) on all servers that are involved in the distributed transaction. If you are using earlier remote servers instead of the recommended linked servers, set the remote proc trans configuration option setting to OFF for the server, or issue a SET REMOTE_PROC_TRANSACTIONS OFF statement before you run any distributed query. If this setting is set to ON, the remote procedure calls are made in a local transaction. Ensure SQL Server startup accout has full permissions on this registry key HKEY_LOCAL_MACHINESoftwareMicrosoftMSSQLServer Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>