Tuesday, June 15, 2010

RPC calls between SQL Servers

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.

The error:
OLE DB provider "SQLNCLI" for linked server "[REMOVE-SERVER]" returned
message "The partner transaction manager has disabled its support for
remote/network transactions.".
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
transaction.




The solution:
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.
More Information

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.
More Information

Just to make sure, to execute an external stored procedure from a local one. It should be called as follows:


EXEC [REMOTE-SERVER].[DB].dbo.[StoredProc]

No comments:

Post a Comment