Lately, I've been trying to call an stored procedure from another server without success. The reason is that SQL Servers last versions need some additional configuration in order to make it possible.
In the previous versions of SQL Server, we just needed to add a linked server and then call the procedures. Nowadays, we need to configure the servers in order for them to allow RPC.
OLE DB provider "SQLNCLI" for linked server "[REMOVE-SERVER]" returned
message "The partner transaction manager has disabled its support for
Msg 7391, Level 16, State 2, Procedure [LocalStoredProc], Line 42
The operation could not be performed because OLE DB provider "SQLNCLI"
for linked server "[REMOVE-SERVER]" was unable to begin a distributed
1. Execute sp_addlinkedserver: More Information
2. Configure both SQL servers (the one that calls and the one that is being called)
2.1. Start the Component Services administrative tool. To do this, click Start, click Run, type dcomcnfg.exe, and then click OK.
2.2. In the console tree of the Component Services administrative tool, expand Component Services, expand Computers, right-click My Computer, and then click Properties.
2.3. Click the MSDTC tab, and then click Security Configuration.
Up to this point it worked for me. However, should you have still issues with RPC, you could always see for more configurations here
And also, If you have issues with nested transactions when using explicit transactions. Verify that: SET XACT_ABORT is ON in your session.
Just to make sure, to execute an external stored procedure from a local one. It should be called as follows: