Example of remote table definition

The following example defines the remote Adaptive Server table authors, starting with the server definition:

1. Define a server named SYBASE. Its server class is ASEnterprise, and its name in the interfaces file is SYBASE:

   exec sp_addserver SYBASE, ASEnterprise, SYBASE
   

2. Define a remote login alias. This step is optional if the username and password are the same on both servers. User “sa” is known to remote server SYBASE as user “sa,” password “timothy”:

   exec sp_addexternlogin SYBASE, sa, sa, timothy
   

3. Define the remote authors table:

   create existing table authors 
   ( 
   au_id        varchar(11)      not null,
   au_lname     varchar(40)      not null,
   au_fname     varchar(20)      not null, 
   phone        char(12)         not null,
   address      varchar(40)      null,
   city         varchar(20)      null,
   state        char(2)          null,
   country      varchar(12)      null,
   postalcode   char(10)         null
   )
   EXTERNAL TABLE at "SYBASE.pubs2.dbo.authors"
   
   

4. Update statistics on tables to ensure reasonable choices by the query optimizer:

   update statistics authors
   

5. Execute a query to test the configuration:

   select * from authors where au_lname = 'Carson'