3

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?

Christopher Johnson
  • 2,629
  • 7
  • 39
  • 70
  • Is your 'distributed Transaction Coordinator' Service Started? – Adam Wenger Oct 22 '12 at 18:33
  • 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 Oct 22 '12 at 18:48
  • IIRC, the DTC service needs to be started on both the client, and server machine. – Adam Wenger Oct 22 '12 at 18:54
  • I just asked the dba guys...that service is started and running on both machines...any other ideas? – Christopher Johnson Oct 22 '12 at 18:57
  • 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 Oct 22 '12 at 19:01
  • well, as I said in my OP, I can call the remote sproc w/o any issues. It's only when I add the INSERT INTO statement above the remote procedure call that I run into this problem. I could (I guess), just call the remote sproc, fill an object in code, then pass that object back to sql to fill the table, but that seems unnecessary....surely there's a way to do all of this with just sql? – Christopher Johnson Oct 22 '12 at 19:07
  • I think these guys discussed in pretty much detail: http://stackoverflow.com/questions/7694/how-do-i-enable-msdtc-on-sql-server – Farfarak Oct 23 '12 at 08:56

1 Answers1

2

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

Christopher Johnson
  • 2,629
  • 7
  • 39
  • 70