8

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".

Improve this question
2
  • I assume you have enabled MS DTC and his network access. Your link to the IBM documentation might give you a clue: "will fail with reason code of 2". Also check the eventlog for DTC errors.
    – Jens W.
    Mar 13, 2014 at 17:49
  • So you want a two phase commit? I.e. If the commit on the DB2 side fails, also rollback the changes on the SQL side that are in the same transaction? That's a very specific requirement- are you sure you really need to do that?
    – Nick.McDermaid
    Dec 14, 2017 at 14:01

2 Answers 2

Reset to default
8

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

Improve this answer
1
  • Excellent. Firewall was the issue for me.
    – squillman
    Aug 13, 2020 at 20:10
2

Check the DTC and SQL Browser service is running under right service account.

also check this following MS-BOL links for more configuration help.

Recommended MSDTC settings for using Distributed Transactions in SQL Server

you can use this tool to identify if your system has proper MSDTC configuration.

2012 Best Practices Analyzer

Last but not least

Understanding XA Transactions

let us know some sample query you are trying.

Improve this answer
3
  • What is the "right" service account? Should they be running under the same service account? There's also a sample query in my question.
    – Mansfield
    Mar 13, 2014 at 18:40
  • the service account which has access to both disk location where SQL Server and DB2 is installed or any shared location the query might be trying access. because as you said the query ran for sometime and then again stopped or it is not working all the time. I am not sure what is the query, for example if it is a dumping the sql data in excel file and then importing in to db2?
    – Anup Shah
    Mar 13, 2014 at 18:45
  • oh my bad if the only update query you mentioned in the post then never mind.
    – Anup Shah
    Mar 13, 2014 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.