Performing a Join Between Two Remote Tables

Component Integration Services allows you to perform joins across remote tables.

Edit the interfaces file using dsedit.
Use sp_addserver to add entries to the sysservers system table. On the server originating the call, there must be an entry for each remote server that is to be called. The following examples create entries for the local server named big_server and for the remote server big_server of class ASEnterprise
```
sp_addserver big_server, local
sp_addserver CTOSDEMO, ASEnterprise, big_server
```
Use create existing table to enable the definition of existing (proxy) tables. When the server processes this command, it does not create a new table. Instead, it checks the table mapping and verifies the existence of the underlying object. If the object does not exist (either host data file or remote server object), the server rejects the command and returns an error message to the client.
After you define an existing table, issue an update statistics command for that table. This helps the query optimizer make intelligent choices regarding index selection and join order.
This demonstrates creating remote SAP ASE tables publishers and titles in the sample pubs2 database mapped to a local server:
1. Define a server named big_server. Its server class is ASEnterprise, and its name in the interfaces file is big_server:
```
exec sp_addserver big_server, ASEnterprise, big_server
```
2. Define a remote login alias. This step is optional. User “sa” is known to remote server big_server as user “sa,” password “timothy”:
```
exec sp_addexternlogin big_server, sa, sa, timothy
```
3. Define the remote publishers table:
```
create proxy_table publishers
at "big_server.pubs2.dbo.publishers"
```
4. Define the remote titles table:
```
create proxy_table titles
at "big_server.pubs2.dbo.titles"
```

Use the select statement to perform the join. For example:

select Publisher = p.pub_name, Title = t.title
 from publishers p, titles t
 where p.pub_id = t.pub_id
 order by p.pub_name