Error using distributed transaction in SQL Server 2008 R2

Question

I am using SQL Server 2008 R2.

I get the following error when I try to execute SP from the java side. The same SP works well when I use the query browser to run.

I also tried to use hot fix, but it does not work.

ERROR :: The operation could not be completed because the OLE DB provider "SQLNCLI10" for the linked server "server name" could not start the distributed transaction. during transaction

Answer 1

Inside SQL Server Management Studio, expand the Server Objects node, then Associated Server, then right-click the appropriate server and select Properties. Select the Server Settings page and verify that Enable Distributed Transaction Promotion is set to False

Or you can do it using T-SQL :

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