Using INSERT INTO...EXEC AT linked server into temp table fails with Msg 7391

In my testing (to another instance on the same server, not on a separate server), this worked if I had the Linked Server option of Enable Promotion of Distributed Transactions for RPC set to "False". You can accomplish that via the following command:

EXEC master.dbo.sp_serveroption
       @server = N'{linked_server_name}',
       @optname = N'remote proc transaction promotion',
       @optvalue = N'false';

This worked with the Distributed Transaction Coordinator (MSDTC) both ON (Running) and OFF (Stopped).

If you normally need the "remote proc transaction promotion" option set to "True", and if setting it to "False" allows this INSERT...EXEC to work, then you can set up another Linked Server with all of the same properties except this one option being different.

The main drawback to disabling "remote proc trans promotion" is that well, it's not a transaction at the remote server. So, if an error occurs there, you won't get the data inserted locally (obviously), but if there were any DML statements run remotely, those could still commit (depending on if it was a single statement or multiple statements). Still, you could / should still use proper transaction handling on the remote query (i.e. use the TRY...CATCH construct):

CREATE TABLE #Local ([name] [sysname]);


INSERT INTO #Local ([name])
    EXEC (N'
BEGIN TRY
    BEGIN TRAN;
    SELECT [name] FROM sys.servers WHERE 1/0 = ''a'';
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK TRAN;
    THROW;
END CATCH;
') AT [{linked_server_name}];

P.S. The RPC Out option needs to be enabled / True. This was not mentioned above since according to a comment on the question, this option is already set correctly.

I can use:

SELECT *
INTO #TABLATEMP
FROM Openquery([link-server-name],N' Execute anything ')

Select Campo1, Campo2, Campo3, .... CampoN
FRom #TABLATEMP

DROP TABLE #TABLATEMP

-- That's all!!!