Creating remote servers (SQL)

Use the CREATE SERVER statement to set up remote server definitions.

Prerequisites

You must have the SERVER OPERATOR system privilege.

Context and remarks

Each remote server is accessed using an ODBC driver. A remote server definition is required for each database.

A connection string is used to identify a data source. On Unix platforms, the ODBC driver must be referenced in the connection string as well.

Create a remote server

Use the CREATE SERVER statement to define a remote data access server that links to a remote server.

For example, the following statement defines the remote server RemoteASE. The SQL Anywhere database server connects to an Adaptive Server Enterprise database server using the ODBC connection string specified in the USING clause.

CREATE SERVER RemoteASE
CLASS 'ASEODBC'
USING 'DRIVER=SYBASE ASE ODBC Driver;Server=TestASE;Port=5000;Database=testdb;UID=username;PWD=password';

The following is an analysis of the components of the CREATE SERVER statement.

SERVER This clause is used to name the remote server. In the example, RemoteASE is the remote server name.
CLASS This clause is used to indicate how the SQL Anywhere database server should communicate with the remote server. In the example, ASEODBC indicates that the remote server is Adaptive Server Enterprise (ASE) and that the connection is made using the ASE ODBC driver.
USING This clause specifies the ODBC connection string for the remote server. In the example, the ASE ODBC driver is specified.

Results

The CREATE SERVER statement creates an entry in the ISYSSERVER system table.

Next

Create external login information if required.

Example

The following statement defines the remote server RemoteSA. The SQL Anywhere database server connects to a SQL Anywhere database server using the ODBC Data Source Name (DSN) specified in the USING clause.

CREATE SERVER RemoteSA
CLASS 'SAODBC'
USING 'SQL Anywhere 16 CustDB';

The following statement defines the remote server RemoteLinuxSA. The SQL Anywhere database server connects to a SQL Anywhere database server using the ODBC Data Source Name (DSN) specified in the USING clause. On Unix platforms, the ODBC driver must be specified in the connection string.

CREATE SERVER RemoteLinuxSA
CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere 16;DSN=my_sa_dsn';

The following statement defines the remote server RemoteLinuxASE. The SQL Anywhere database server connects to an Adaptive Server Enterprise (ASE) database server using the ODBC Data Source Name (DSN) specified in the USING clause. On Unix platforms, the ODBC driver must be specified in the connection string.

CREATE SERVER RemoteLinuxASE
CLASS 'ASEODBC'
USING '/opt/sybase/ase_odbc_1500/DataAccess/ODBC/lib/libsybdrvodb.so;DSN=my_ase_dsn';

The following statement defines the remote server RemoteAccessDB. The SQL Anywhere database server connects to a Microsoft Access database using the ODBC DSN MyAccessDataSource which is specified in the USING clause.

CREATE SERVER RemoteAccessDB 
CLASS 'MSACCESSODBC'
USING 'MyAccessDataSource';

Creating remote servers (SQL)

Create a remote server

See also