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
Best Answer
You can prevent using distributed transactions for the linked server by setting server option 'remote proc transaction promotion' to 'false':
Here's the same issue