0
I created a Trigger that fetches data from an SQL Server table and writes or updates data from another table in Oracle if there is a change. All this via Linkedserver. But executing it generates the following error:
The OLE DB provider "Oraoledb.Oracle" for the linked server "INTERFTRIMERP" returned the message "New transaction cannot Enlist in the specified transaction Coordinator. ".
Msg 7391, Level 16, State 2, Procedure Inserts
The operation could not be performed because the OLE DB provider "Oraoledb.Oracle" for the linked server "INTERFTRIMERP" could not start a distributed transaction.
What might be going on?
With SQL Server only tables works normally, now via Linked server to Oracle, generates this error.
Marcio, it wasn’t clear one thing: is Trigger fired from a table in SQL Server or Oracle? If possible provide database versions
– gmsantos
Márcio, you need to ensure that the Oracle driver used in the Linkedserver configuration supports distributed transactions. The problem is probably the transaction in your SQL code. Is this really necessary? An alternative "read-only" would be to read Oracle data before and after starting the change transaction in SQL Server.
– utluiz
@gmSantos, Trigger is triggered when entering or changing data from a table in Sqlserver 2012.
– Márcio Morando
@utluiz, how do I check whether the drive used supports distributed transactions?
– Márcio Morando
@Márciomorando I found a Microsoft KB that describes the error you are going through. Check if it can help you: http://support.microsoft.com/kb/280106/pt-br
– gmsantos
@Márciomorando So, once I had to do this, but it was long ago and it was in another company. I have no way to consult the material I left there. As I recall Oracle has more than one type of Driver. There is the simplest and most complete client with XA support (distributed transactions). Note on the link that the
gmsantosthere’s something there withxa.dll. That must be what’s missing. Unfortunately the configuration of these things depends a lot also on the version of Oracle you are using and also varies if it is 32 or 64 bits. Beware of these differences, do not mix SQL Server 34 with DLL 64.– utluiz
Did you find a solution? Poste as an answer to help other people.
– Maniero