The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "(null)" was unable to begin a distributed transaction

Question

I am using Connection Pooling to connect with SQL Server 2008.

With JNDI Name using XA connection to get access across DB server. Now my all query execute properly except query which use OPENROWSET.

Now this OPENROWSET runes fine with normal connection but while executing with XA connection it gives an exception :

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "(null)" was unable to begin a distributed transaction.

Can any one help me for this issue? If you don't understand please replay, I'll explain more.

Germinal

Answer 1

Try this:

Connect to your database in SQL Server Management Studio, expand Server Objects, then Linked Server, then right click on the linked server in question and choose 'Properties.' Select the 'Server Options' page, and make sure 'Enable Promotion of Distributed Transactions' is set to 'False'

Or you can do it with T-SQL:

USE master;
EXEC sp_serveroption '<<your linked server name>>', 'remote proc transaction promotion', 'false';

Disencumber