8

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.

0
7

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.