OLe DB provider "SQLNCLI" for linked server could not start distributed transaction

Question

I am trying to call a stored procedure in SQL Server 2008 and store the extracted data in a local temp table.

When I try to start it, I get the following error:

The operation could not be completed because the OLe DB provider "SQLNCLI" for the linked server could not start a distributed transaction

My code is as follows:

create table #temp( col1 as int, col2 as varchar(50) ) insert into #temp exec [192.168.0.9].[db1].[dbo].[tablename] @usr_id=3 

Answer 1

You can prevent the use of distributed transactions for a linked server by setting the server parameter "remote transaction transaction promotion" to "false":

 EXEC sp_serveroption 'servername', 'remote proc transaction promotion', 'false' 

Here is the same problem

Answer 2

the linked server was unable to start distributed transactional errors due to problems in MSDTC (MS Distributed Transaction Coordinator). Problems may arise due to a number of problems. MSDTC is not enabled, it is blocked by the firewall and others.

If you need transactions, you should debug the problem almost on your own, as it is the environment. If you can rewrite to avoid transaction, your life will be easier. To verify that this is an MSDTC problem, write a simple query that will not depend on MSDTC. eg.

 create table #temp( col1 as int, col2 as varchar(50) ) insert into #temp select col1, col2 from [192.168.0.9].[db1].[dbo].[tablename] where usr_id=3 

If this works, it is definitely MSDTC (and possibly a preventable problem)

- Added it. Spent a bit looking for MSDTC debugging. http://www.sqlwebpedia.com/content/msdtc-troubleshooting was pretty good, as was http://www.mssqltips.com/sqlservertip/2083/troubleshooting-sql-server-distributed-transactions-part-1-of -2 / togehter they cover almost everything I can remember to debug MSDTC problems (and some others too).