SQL - OLE DB provider "SQLNCLI10" for linked server "srvname" returned message "The partner transaction manager has disabled its support for remote/network transactions.". Msg 7391, Level 16, State 2,

If the problem is with linked server - Is your DTC configured correctly, and your DTC is running in both server?

I should add a comment what I am doing.

 I am calling a store procedure from  Link server as below. it will return me a table of information

EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0


I works fine.  However when I try to insert the result to a local temporary table, I got this error.

INSERT INTO #MyLocalTempTable (Label , Amount )
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0


Prior to this I have tested this procedure when I connect directly to the database engine without the linkserver configuration. That works fine.

INSERT INTO #MyLocalTempTable (Label , Amount )
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0

It looks like I can not insert data to a table from a remote procedure call. How do I get around this problem.

anujnb,

I was able to run the stored procedure of the Linked Server
I got output table of calling stored procedure in the result tab.

I was only unable to insert the result into a local tmp table.

If security is not set probably, would it means I will not even able to execute the stored procedure and get back the output in the result tab?

anujnb,

I have attached the MSDTC config from the client side.  I am waiting from my IT to give me the server side configuration
Capture1.JPG

This happens when you have a distributed transaction involving more than one SQL Server, so for this you need to configure MSDTC settings. Most of the time the above steps will solve this issue.

After I modify both client and server,  I got this error.


OLE DB provider "SQLNCLI10" for linked server "WINFUND" returned message "No transaction is active.".

This is what I try to execute
INSERT INTO #MyLocalTempTable (Label , Amount )
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0

it is still fine if I execute
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0

I still do not understand the difference this two execute.
the 2nd execute send the output to result tab
and the first execute attemp to send the result to the table,
Logically, I would expect the output data do arrive to my local machine running SMSS.  What stop me to insert the data to my table but allow the same data appear in the result tab?

Did you configured the MSDTC both at linked server and the Server?

I still do not understand the difference this two execute.
the 2nd execute send the output to result tab
and the first execute attemp to send the result to the table,

When you do EXEC only this will works as this is simply returning rows, if yous SP has any DML or DDL that modifies any objects in the linked server then it may fail, to avoid this you must configure the MSDTC properly. When you do INSERT INTO with EXEC, its a cross server transaction.

The error you got is a known problem, that explains it here

anujnb

Thanks for your explanation.  I will check again the setting and reboot the server.

anujnb,

what is the difference between

INSERT INTO #MyLocalTempTable (Label , Amount )
EXEC [LINKSERVERNAME].[database].[dbo].[storedprocedure] 1, 0

and

INSERT INTO #MyLocalTempTable (Label , Amount )
select *  FROM [LINKSERVERNAME].[database].[dbo].[TABLE1]

Are both consider to be a cross server transaction
The reason why I asked,  the insert and  select work but not insert and exce.

Thanks.  I manage to reverse engineer the stored procedure I want ocally.