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

Question

I am using SQL Server 2014. I want to execute EXEC (@remotecmd) AT [server_name]; (@remotecmd is a dynamic sql not a stored procedure) into ##eapb. My code is

insert into ##eapb
EXEC (@remotecmd) AT [ADSQLDB3S\DEV];

But I get the error:

OLE DB provider "SQLNCLI11" for linked server "server_name" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 71
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "server_name" was unable to begin a distributed transaction.

If I remove the insert into ##eapb, I have no error.

The Linked Server has the RPC Out option set to True.

Answer 1

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.

Answer 2

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!!!

Answer 3

This way you can manipulate your query with parameters that you want to add and at the same time carry out the insert into by executing the EXEC AT LINKEDSERVER command.

The only drawback is that you have to do the CREATE TABLE #auxiliary initially.

DECLARE @v_dat_sfk int = 20230101;
DECLARE @v_sql_query nvarchar(max) = '';
DROP TABLE IF EXISTS #v_aux_table_canceled_returns;
CREATE TABLE #v_aux_table_canceled_returns (
TICKET_ID varchar(50),
QTY int,
DAT_SFK int
)
SET @v_sql_query = 'DECLARE @v_dat_sfk int = ###v_dat_sfk;
 DROP TABLE IF EXISTS #allReturnsModified;
 SELECT auDet.*
 INTO #allReturnsModified
 FROM ...
 WHERE 1 = 1
 AND ( CONVERT(VARCHAR,COALESCE(auCab.DT_MOD, auCab.DT_CRI),112) = @v_dat_sfk
 OR CONVERT(VARCHAR,COALESCE(auDet.DT_MOD, auDet.DT_CRI),112) = @v_dat_sfk
 )
 
 SELECT cab.TICKET_ID,
 cab.QTY,
 cab.DAT_SFK
 FROM #allReturnsModified AS auDet
 INNER JOIN ...
 ';
SET @v_sql_query = REPLACE(@v_sql_query, '###v_dat_sfk', @v_dat_sfk);
INSERT INTO #v_aux_table_canceled_returns
EXEC (@v_sql_query) AT [LINKED_SERVER]