I want to execute a remote stored proc and put the results in a local table.
MachineB is a linked server on MachineA.
I can do this succesfully on MachineA....
I can also do either of these without any trouble....
SELECT * INTO local_copy FROM [MachineB].master.dbo.sysdatabases
INSERT INTO local_copy(name, dbid) SELECT name, dbid FROM [MachineB].master.dbo.sysdatabases
...assuming in the 2nd case that table 'local copy' has the appropriate matching columns.
But I CAN'T do this..... (and the target table is correct)
INSERT INTO local_copy
I get the error ""The transaction manager has disabled its support for remote/network transactions." or sometimes "The partner transaction manager has disabled its support for remote/network transactions." Whether or not the word 'partner' appears may be OS-level dependent.
I have SET IMPLICIT_TRANSACTION OFF set.
What I don't get is why INSERT ... remote-SELECT is not a distributed transaction and INSERT ... remote-EXEC is
It's probably not going to help to use component services to allow remote transactions, because I'm not going to be able to do inbound authentication. (different domains wo/trust) And inbound authentication WILL be set on some targets because it HAS to be set on clusters. I don't like this... it's Microsoft forcing Windows authentication down our throats, even that that's not practical in lots of scenarios - such as when some participating nodes are some OS other than Windows. So for me the solution seems to be for this NOT to be a distributed transaction. Why should it be, when INSERT...remote-select doesn't have to???? Besides, only one machine is getting updated. No need to coordinate changes on multiple machines.