Sql-server – How to enable distributed transactions for a linked server

distributed-transactionsiserieslinked-serversql serversql-server-2012

I have a SQL Server 2012 instance with a linked server pointing to a db2 database on an IBM Iseries system using the System i Access ODBC Driver.

I'm trying to enable distributed transactions. I believe this provider does support them as per IBM documentation (unless I'm misunderstanding that link).

I'm running the following query:

BEGIN TRANSACTION
    UPDATE LINKEDSERVER.LINKEDDATABASE.SCHEMANAME.TABLENAME SET COLUMN = VALUE

    SELECT CAST('qwerty' as integer) -- we want an error here so the transaction fails
COMMIT TRANSACTION

I get the following error:

OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][System i Access ODBC Driver]Enlist with DTC phase failed. 2".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" was unable to begin a distributed transaction.

All the research I've done on this error has met with people wanting to disable distributed transactions, not get them working. I've already set "Enable Promotion of Distributed Transactions" to true on the linked server properties.

Any ideas how I can get this working?

EDIT: More information/update. I was about ready to give up on this for now and wait for help, when I noticed this in the windows logs:

A caller has attempted to register an XA resource while XA transactions are disabled.

I found this link which told me how to modify the settings for the MSDTC.
After playing with the settings and restarting my server things started to work (kind of):
(specifically, I enabled network DTC access, allow inbound/outbound/enable XA transactions).

enter image description here

At present, the query runs and commits/rolls back as expected, but seems to work some of the time, but error other times. The error is:

OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][System i Access ODBC Driver]Internal driver error."

EDIT2: Now it's not working at all again. Error message:

OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "ODBC driver could not start new transaction.".
OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[Microsoft][ODBC Driver Manager] Illegal operation while in a transaction server component transaction".

Best Answer

In addition to setting Local DTC Properties, you need to open the firewall on both machines to allow program Distributed Transaction Coordinator to go thru.

Allow Distributed Transaction Coordinator to communicate through Windows Firewall

When testing T-SQL in Query Editor Window, make sure to turn on XACT_ABORT as suggested by Microsoft.

XACT_ABORT must be set ON for data modification statements in an implicit or explicit transaction against most OLE DB providers, including SQL Server. The only case where this option is not required is if the provider supports nested transactions.

In my case, for the Local DTC Properties, I only need these: DTC Settings

Related Question