You must specifically enable relocated joins for each remote server involved. The remote server must be able to connect back into the local server using Component Integration Services (CIS).
To configure relocated joins:
Use sp_serveroption to enable relocated joins to be sent to that remote server:
sp_serveroption servername, "relocated joins",true
On the remote server, verify that:
The remote server has an interface entry for the local server.
There is a sysservers entry (added via sp_addserver).
External logins have been configured.
If you are using dynamically created proxy tables, they are created in tempdb when a relocated join is received. Enable ddl in tran to ensure that tempdb allows proxy tables:
sp_dboption tempdb,"ddl in tran",true
