1

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.

  • 1
    Has the linked server RPC properties = True? – McNets Dec 21 '17 at 14:30
  • Also, make sure your DTC security is setup properly and RPC is allowed between servers (ie firewall, ACLs)support.microsoft.com/en-ca/help/2027550/… – Bob Klimes Dec 21 '17 at 14:39
  • @McNets, it is already set to True. This is not actually the issue. Thanks anyway. – Edgar Allan Bayron Dec 21 '17 at 14:53
2

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.

  • In order for me to successfully run my script with EXEC (@remotecmd) AT [server_name];, I need to change the default properties like "RPC Out" and "Enable Promotion of Distributed Transaction". Would not this give me drawbacks? Thanks. – Edgar Allan Bayron Dec 21 '17 at 15:25
  • 1
    @EdgarAllanBayron "RPC Out" needs to be enabled / True regardless. You said, in comments on the question, that it was already set to True, which is why I didn't mention that option. As far as drawbacks go, 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. This is why I said that IF you need that coordinated transaction for other uses, then create a second Linked Server so you don't give that up. – Solomon Rutzky Dec 21 '17 at 15:30
  • Thanks for the clarification. I still have a lot to learn in term of database' behavior. – Edgar Allan Bayron Dec 21 '17 at 15:37
  • 1
    @EdgarAllanBayron Please see the updates I just made to my answer. I added some clarification and some example code. – Solomon Rutzky Dec 21 '17 at 15:56
  • 1
    @EdgarAllanBayron I'm not entirely sure what you are asking here, but it sounds like a completely different question. Please post it as a new question. – Solomon Rutzky Dec 21 '17 at 16:03

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.