Distributed Transaction Error Only Through Code

Question

I and trying to perform a query against a linked server (SQL Server 2008 linked to Sybase) and select it into a temp table. Is works perfectly though a query window in SQL Management Studio, but when I do it through code (C#) it fails with the error "The operation could not be performed because OLE DB provider "ASEOLEDB" for linked server "MYLINKEDSERVER" was unable to begin a distributed transaction. I am not using a transaction in code with my DbConnection.

This query looks like this:

SELECT * 
INTO #temptable
FROM OPENQUERY([MYLINKEDSERVER], 'SELECT * from table')

Is MSDTC configured the same for all machines involved in the distributed transaction? Are the permissions all correct? — Oded

ctrlalt313373 ctrlalt313373 · Accepted Answer · 2010-11-21T22:32:36

Found the issue. It was a result of connection pooling. It appears that connections were getting reused causing the system to think there was a distributed transaction happening.

Distributed Transaction Error Only Through Code

1 Answers