OLe DB provider "SQLNCLI" for linked server was unable to begin a distributed transaction

Question

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

When I try to run it, I receive the following error:

The operation could not be completed because OLe DB provider "SQLNCLI" for linked server was unable to begin 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 using distributed transactions for the linked server by setting server option 'remote proc transaction promotion' to 'false':

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

Here's the same issue