Here is problem you may encounter using a Linked Server connecting to an Oracle Database. I was trying to create a table in SQL Server with data from an Oracle database using a Linked Server and I received this error: Unable to enlist in the transaction Msg 7391, Level 16, State 2
A new Linked Server error that I have recieved.
OLE DB provider "OraOLEDB.Oracle" for linked server "ccb1" returned message
"Unable to enlist in the transaction.".
Msg 7391, Level 16, State 2, Line 151
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ccb1"
was unable to begin a distributed transaction.
When in doubt I check the documentation about the feature I am using, and I read about transaction promotion. https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration-data-access-transactions/transaction-promotion?view=sql-server-ver15
This is a distributed transaction, and I see this setting under the Linked Server settings.
I see that Enable promotion of distributed transaction for RPC is set to TRUE. Here is what this setting is used for:
Default is True. When enabled any remote query that changes data will invoke the DTC (Distributed Transaction Coordinator).
This process protects such queries by allowing both servers to have a shared transaction that either completes 100% or 0% - just
like a regular transaction within a local query. When disabled the DTC is not invoked and a failed remote query can fail
and not be completely rolled back.
For this distributed transaction to work, I need to change the setting to FALSE.
After changing the setting to FALSE, I rerun the sql statement and now it completes sucessfully.
Linked Servers can be tricky when querying across different platforms, but is a very handy feature for pulling data from different systems into a single database.