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