inserting to a local table from a linked stored procedure

Question

I have a sproc that is called from .net code which does an update on a local table and then is supposed to insert to a different local table values that are obtained from a remote procedure call to a linked server.

I get this error message every time I try to insert the results from the linked server. If i take out the insert into statement, but leave the remote procedure call in place, it works fine. Here is the error:

System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "MyLinkedServer" was unable to begin a distributed transaction. OLE DB provider "SQLNCLI10" for linked server "MyLinkedServer" returned message "The transaction manager has disabled its support for remote/network transactions.".

this is the simple code I'm using:

INSERT INTO MyLocalTable
EXEC [MyLinkedServer].[MyRemoteDatabase].[dbo].[usp_MySproc] @MyParam

any ideas what's wrong here or any suggestions of how I can insert the results from the remote procedure to a local table?

Is your 'distributed Transaction Coordinator' Service Started? — Adam Wenger
I'm not sure. which server would that need to be started on? The remote one or the local one? In either case, I won't have access to that. That would be a different team (DBA team) and they're not keen on doing admin stuff like that. Is there another way to get the data remotely and save it locally? — Christopher Johnson
IIRC, the DTC service needs to be started on both the client, and server machine. — Adam Wenger
I just asked the dba guys...that service is started and running on both machines...any other ideas? — Christopher Johnson
Your error message says "The transaction manager has disabled its support for remote/network transactions." At this point you would probably want to have the DBA/IT guys check the configurations of the DTC to see if they allow remote connections, which the error message leads me to believe it is not — Adam Wenger

Christopher Johnson Christopher Johnson · Accepted Answer · 2012-10-23T16:39:20

one of the sql guys was able to resolve this by changing the "enable promotion of distributed transactions" to false.

inserting to a local table from a linked stored procedure

1 Answers