Unable to enlist in the transaction using a Linked Server. Msg 7391, Level 16, State 2

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s