Hi guys

A pretty long title? Well, recently I came across a situation where I needed a trigger with MS SQL server table to insert some information into our Oracle database.

The MS SQL Server is hosted in a Windows 64 bit OS, with Oracle 11g 64Bit client installed (For 64Bit OS, you must install Oracle client 64Bit for the Oracle OLEDB provider)

I did some sample inserts using the Management studio and created a trigger like following with one of the sample tables:

create trigger addRecordsToERPTable on [UNIS].[dbo].[tRajesh]
after insert
as
begin
  insert into [XYZ].[APPS].[XXFPPUNCHM] 
  (PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE)
  Select PUNCH_TIME,MACHINE_NAME,EMPLOYEE_NUMBER,PUNCH_TYPE
  FROM inserted
end
go

So the idea was pretty simple, like an audit, as soon as the SQL table “rRajesh” has a new row inserted, the after insert trigger should sent the same row to underlying table over Oracle. Instead I started getting the following error:

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

I’m not very familiar with MS SQL or the complexities related to Linked Server environments. So, started my next series of Google searches. I referred tons of discussions, however was not getting anywhere with the dreaded situation. During the frantic search for a solution, I executed the instructions available over different links.

https://stackoverflow.com/questions/6999906/distributed-transaction-error

https://community.oracle.com/thread/2265534

Even after making changes as mentioned with the above threads, I still kept on receiving the same errors while a row was inserted into my SQL sample table. So I continued searching for a solution and came across a thread

https://microsoft.public.sqlserver.security.narkive.com/WDGBVTrk/msdaora-was-unable-to-begin-a-distributed-transaction-why-sql-oracle

This thread was pointing towards a Microsoft’s post addressing this particular situation.

http://support.microsoft.com/kb/280106

Although the article addresses pretty Old OS and Oracle environments, the solution is still applicable on later OS and Oracle clients. For example, My MS SQL Server is installed over Windows 2008 R2 and the Oracle client I am using with the server is 11G R2 64Bit.

Let us see quickly what Microsoft provides as a solution.

I checked the registry of my server and found something pretty interesting like below:

Now, Oracle names almost all their major dll files in a particular fashion. Most of the times you will find the dll files having the major version numbers by the end of the filename, for example, if your Oracle database is 8.0, your client dll file will be “Oraclient8.dll” and if you are using Oracle 11g, the filename would be “Oraclient11.dll”

After taking a full backup of the registry, I modified the values with 11g specific & restarted the Server (as per the instructions available for Oracle 8.1 in the Microsoft document.)

Once the server started, I went ahead and tried to insert a new row into my sample table and that was it. No more errors and the row was inserted to both MS SQL table and Oracle table at the same time.

So if you were frantically searching for a solution, this post may help you to resolve it.

regards,

rajesh