How to “Unable to enlist in the transaction” with Oracle linked server from MS SQL Server

I am working as a SQL developer. I have a setup where I use MS SQL Server with a linked Oracle server. Our database has views on the Oracle database. I have in-house written software (I'm the lead developer) which uses this setup.

I've had this setup for 5 years and it's working fine in production, on a beta server and all our development boxes, except mine.

I recently rebuilt my machine using Windows 10 (I'm the first using Windows 10 for this). I'm using the Oracle Database 12c Release 1 ( client (we previously used 11g, but Windows 10 appears to require 12c).

When I try to perform an update against a view on the Oracle server, within a transaction, it fails. This works fine on any of our other servers, so I'm sure it's either a Windows 10 issue, Oracle 12c client issue, or I've somehow messed up the setup.

I've done all the setups in the past (production, beta, and all developer machine setups), so I have quite a bit of experience doing this over the years. The problem is only with a transaction. Non-transactional queries and updates work just fine. Here's an example that will fail.

begin tran
update CLIENT set SUPERVISOR_FLAG = 'Y' where CLIENT_CODE = 'XYZ123'
commit tran

The error I get is:

OLE DB provider "OraOLEDB.Oracle" for linked server "REMOTESERVER" returned message "Unable to enlist in the transaction.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because of OLE DB provider "OraOLEDB.Oracle" for linked server "REMOTESERVER" was unable to begin a distributed transaction.

Multiple SQL Servers link to the same back-end Oracle server and they have no problem with this, so I can rule out a configuration issue on the Oracle server.

These are my DTC settings and these are the same as other machines that are working:


For my Oracle Provider for Ole DB (OraOLEDB.Oracle) settings, I have "Dynamic parameters", "Nested queries", "Allow inprocess" and "Supports 'Like' operator" enabled. This is also the same as other machines.

Under Server Options the settings are:

  • Collation Compatible - False
  • Data Access - True
  • RPC- True
  • RPC Out - True
  • Use Remote Collation - True
  • Collation name - [blank]
  • Connection Timeout - 0
  • Query Timeout - 0
  • Distributor - False
  • Publisher - False
  • Subscriber - False
  • Lazy Schema Validation - False
  • Enable Promotion of Distributed Transactions - True
    Again, these match the settings on other working servers.

I'm stumped. I'd really appreciate any help anyone can offer on this.


Not sure if this helps, but this is the DTC trace:

pid=10580      ;tid=10624      ;time=07/19/2018-13:02:06.767   ;seq=1          ;eventid=TRANSACTION_MANAGER_STARTED_2            ;;"TM Identifier='(null)                                            '" ;"MS DTC started with the following settings: Security Configuration (OFF = 0 and ON = 1): Network Administration of Transactions = 1, Network Clients = 1, Inbound Transactions = 1, Outbound Transactions = 1, Transaction Internet Protocol (TIP) = 0, XA Transactions = 1, MSDTC RPC Security = Mutual Authentication Required, Account = NT AUTHORITY\NetworkService, Firewall Exclusion = 0, Transaction Bridge Installed = 0, Filtering duplicate events = 1."
pid=10580      ;tid=10624      ;time=07/19/2018-13:02:06.767   ;seq=2          ;eventid=TRACE_SETTINGS                           ;;"TM Identifier='(null)                                            '" ;"Trace Configuration (OFF = 0 and ON = 1): Tracing Of DTC = 1, Tracing Of Transactions = 1, Tracing Of Aborted Transactions = 1, Tracing Of Long-Lived Transactions = 1, Tracing Of All Transactions = 0, Max Limit on Memory Buffers = 0."
pid=10580      ;tid=17072      ;time=07/19/2018-13:02:35.182   ;seq=3          ;eventid=TRANSACTION_BEGUN                        ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c     ;"TM Identifier='(null)                                            '" ;"transaction has begun, description :'user_transaction'"
pid=10580      ;tid=17072      ;time=07/19/2018-13:02:35.182   ;seq=4          ;eventid=RM_ENLISTED_IN_TRANSACTION               ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c     ;"TM Identifier='(null)                                            '" ;"resource manager #1003 enlisted as transaction enlistment #1. RM guid = '452dc8f8-8bbd-4da4-b3e2-e1e29d417a15'"
pid=10580      ;tid=17072      ;time=07/19/2018-13:02:36.104   ;seq=5          ;eventid=RECEIVED_ABORT_REQUEST_FROM_BEGINNER     ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c     ;"TM Identifier='(null)                                            '" ;"received request to abort the transaction from beginner"
pid=10580      ;tid=17072      ;time=07/19/2018-13:02:36.104   ;seq=6          ;eventid=TRANSACTION_ABORTING                     ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c     ;"TM Identifier='(null)                                            '" ;"transaction is aborting"
pid=10580      ;tid=17072      ;time=07/19/2018-13:02:36.104   ;seq=7          ;eventid=RM_ISSUED_ABORT                          ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c     ;"TM Identifier='(null)                                            '" ;"abort request issued to resource manager #1003 for transaction enlistment #1"
pid=10580      ;tid=17072      ;time=07/19/2018-13:02:36.104   ;seq=8          ;eventid=RM_ACKNOWLEDGED_ABORT                    ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c     ;"TM Identifier='(null)                                            '" ;"received acknowledgement of abort request from the resource manager #1003 for transaction enlistment #1"
pid=10580      ;tid=17072      ;time=07/19/2018-13:02:36.104   ;seq=9          ;eventid=TRANSACTION_ABORTED                      ;tx_guid=98d30080-f9d4-46cb-a35c-af47629b377c     ;"TM Identifier='(null)                                            '" ;"transaction has been aborted"
pid=10580      ;tid=18812      ;time=07/19/2018-13:02:57.188   ;seq=10         ;eventid=CHECKPOINTING_STOPPED                    ;;"TM Identifier='(null)                                            '" ;"MSDTC is suspending the checkpointing of transactions due to lack of activity"
pid=10580      ;tid=18812      ;time=07/19/2018-13:02:57.188   ;seq=11         ;eventid=TRACING_STOPPED                          ;;"TM Identifier='(null)                                            '" ;"MSDTC is suspending the tracing of long - lived transactions due to lack of activity"
pid=10580      ;tid=17072      ;time=07/19/2018-13:04:46.678   ;seq=12         ;eventid=TRACING_STARTED                          ;;"TM Identifier='(null)                                            '" ;"MSDTC is resuming the tracing of long - lived transactions"

Thanks & Regards

I know it's an old article, but have you read this