Thursday, October 20, 2011

Errors Executing Queries On Linked Servers - RPC, MSDTC, and You

At my company, we produce software that (among many other things) processes large amounts of data that join tables from one database on one SQL Server to tables in another database on a completely separate SQL Server.  The second server is configured as a Linked Server on the first.
Sounds simple, right?  But to actually get things rolling, I ran into a few issues.

The first error I encountered was:
Server 'MYSERVER' is not configured for RPC

Merely configuring the Linked Server properly fixed this one.
By default, both RPC (i.e. RPC inbound) and RPC Out are disabled, just enable them in the Server Options portion of the Linked Server Properties:




The next time we attempted to process the data, we got a different error message:
MSDTC on server 'MYSERVER' is unavailable

This was the simplest of my issues.  The Distributed Transaction Coordinator service merely needed to be started.  I fired up the Services console and started the service:



I then edited the properties of the service and set it to Automatic:

The final error was the one that gave me the most trouble:
The operation could not be performed OLD DB provider "SQLNCLI10" for linked server "MYSERVER" was unable to begin a distributed transaction.
The transaction manager has disabled its support for remote/network transactions.

This one was a bit harder to track down, but I found that you need to launch Component Services.  Expand Component Services and then browse to Computers -> My Computer -> Distributed Transaction Coordinator.  Right-click Local DTC in the center pane and then click Properties:


Quite a few things need to be changed on the Security tab.  First check the box to enable Network DTC Access and then check all four available Allow options:
  • Allow Remote Clients
  • Allow Remote Administration
  • Allow Inbound
  • Allow Outbound

Finally, you'll need to select an authentication method.  Unless you are dealing with clustered servers (I'm not in my environment), you'll need to select No Authentication Required.

My hope is that someone in the same situation finds this information useful.

Thanks!
Matt

4 comments:

  1. Hi Matt, thank you for this post. It helps a lot. I'll bookmark this page for future reference.

    ReplyDelete
  2. Hi Matt, I have a similar situation but the error I am getting is as below:

    OLE DB provider "SQLNCLI10" for linked server "ARCHIVAL_DB01" returned message "No transaction is active.".
    Msg 0, Level 11, State 0, Line 0
    A severe error occurred on the current command. The results, if any, should be discarded.

    Can you please help me out on this.

    ReplyDelete
    Replies
    1. Did you get any solution to this sai nath? It will be very much helpful.

      Thanks!

      Delete
  3. thank you for this post. It saved me a ton of time.

    ReplyDelete