Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 at 17:49

1 Answer 1

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.

share|improve this answer
    
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 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 at 18:45
    
oh my bad if the only update query you mentioned in the post then never mind. –  Anup Shah Mar 13 at 18:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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