Search code examples
sql-server-2008stored-procedureslinked-server

inserting to a local table from a linked stored procedure

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?

Solution

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