We have a setup where we use MS SQL Server with a linked Oracle server. Our database has views on the Oracle database. We have in-house written software (I'm the lead developer) which uses this setup.
We'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 (12.1.0.2.0) 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
SET XACT_ABORT ON;
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 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:
[Image attachment]
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.
Thanks & Regards
Camillelola