You must specifically enable relocated joins for each remote server involved. The remote
server must be able to connect back to 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