Relocated joins must be specifically enabled for each remote server involved. This configuration must be done because 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
In order for Adaptive Server to establish a CIS connection back to the local server, verify the following on the remote server:
The remote server has an interface entry for the local server
A sysservers entry exists (added via sp_addserver)
External logins have been configured
If you are using dynamically created proxy tables, proxy tables 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
