CREATE SERVER statement

Use this statement to create a remote server.

Syntax 1

CREATE SERVER server-name
CLASS server-class
USING connection-info
[ READ ONLY ]

server-class :
  SAODBC | ASEODBC | DB2ODBC | MSSODBC
| ORAODBC | MSACCESSODBC | MYSQLODBC | ULODBC 
| ADSODBC | ODBC | SAJDBC | ASEJDBC

connection-info :
  { computer-name:port-number [/dbname ] | data-source-name | sqlanywhere-connection-string }

Syntax 2

CREATE SERVER server-name
CLASS 'DIRECTORY'
USING using-clause

using-clause :
ROOT=path
[ ;SUBDIRS=n ]
[ ;READONLY={ YES | NO } ]

Parameters

CLASS clause Specifies the server class you want to use for a remote connection. Server classes contain detailed server capability information. The DIRECTORY class is used in Syntax 2 to access a directory on the local computer.
USING clause In Syntax 1, the USING clause supplies a connection string for the database server. The appropriate connection string depends on the driver being used, which in turn depends on the server-class.
If an ODBC-based server class is used, the USING clause is the data-source-name. The data-source-name is the ODBC Data Source Name.

For SQL Anywhere remote servers (SAODBC server classes), the connection-info parameter can be any valid SQL Anywhere connection string. You can use any SQL Anywhere connection parameters. For example, if you have connection problems, you can include a LOG connection parameter to troubleshoot the connection attempt.

For more information about SQL Anywhere connection strings, see Connection parameters.

On Unix platforms, you need to reference the ODBC driver manager as well. For example, using the supplied iAnywhere Solutions ODBC drivers, the syntax is as follows:
USING 'driver=SQL Anywhere 11;dsn=my_dsn'
If a JDBC-based server class is used, the USING clause is of the form hostname:portnumber [/dbname], where:
- hostname The computer the remote server runs on.
- portnumber The TCP/IP port number the remote server listens on. The default port number for SQL Anywhere is 2638.
- dbname For SQL Anywhere remote servers, if you do not specify a dbname, then the default database is used. For Adaptive Server Enterprise, the default is the master database, and an alternative to using dbname is to another database by some other means (for example, in the FORWARD TO statement).
  In Syntax 2, the USING clause specifies the following values for the local directory:
ROOT clause The path, relative to the database server, that is the root of the directory access class. When you create a proxy table using the directory access server name, the proxy table is relative to this root path.
SUBDIRS clause A number between 0 and 10 that represents the number of levels of directories within the root that the database server can access. If SUBDIRS is omitted or set to 0, then only the files in the root directory are accessible via the directory access server. You can create proxy tables to any of the directories or subdirectories available via the directory access server.
READONLY clause Specifies whether the files accessed by the directory access server can be modified. By default, this is set to NO.
CREATEDIRS clause Specifies whether directories can be created using the directory access server. By default, this is set to NO.

Remarks

When you create a remote sever, it is added to the ISYSSERVER system table.

Syntax 1 The CREATE SERVER statement defines a remote server.

For more information about server classes and how to configure a server, see Server classes for remote data access.

Syntax 2 The CREATE SERVER statement lets you create a directory access server that accesses the local directory structure on the computer where the database server is running. You must create an external login for each database user that needs to use the directory access server. On Unix, the database server runs as a specific user, so file permissions are based on the permissions granted to the database server user.

For more information about directory access servers, see Using directory access servers.

Permissions

Must have DBA authority to execute this command.

Not supported on Windows Mobile.

Side effects

Automatic commit.

Standards and compatibility

SQL/2003 Vendor extension.

Example

The following example creates a SQL Anywhere remote server named testsa, located on the computer named apple and listening on port number 2638, use:

CREATE SERVER testsa
CLASS 'SAJDBC'
USING 'apple:2638';

The following example creates a remote server for the JDBC-based Adaptive Server named ase_prod. Its computer name is banana and its port number is 3025.

CREATE SERVER ase_prod
CLASS 'asejdbc'
USING 'banana:3025';

The following example creates a remote server for the Oracle server named oracle723. Its ODBC Data Source Name is oracle723.

CREATE SERVER oracle723
CLASS 'oraodbc'
USING 'oracle723';

The following example creates a directory access server that only sees files within the directory c:\temp:

CREATE SERVER diskserver0
CLASS 'directory'
USING 'root=c:\temp';
CREATE EXTERNLOGIN DBA TO diskserver0;
CREATE EXISTING TABLE diskdir0 AT 'diskserver0;;;.';

-- Get a list of those files.
SELECT permissions, file_name, size FROM diskdir0;

The following example creates a directory access server that sees nine levels of directories:

-- Create a directory server that sees 9 levels of directories.
CREATE SERVER diskserver9
CLASS 'directory'
USING 'ROOT=c:\temp;SUBDIRS=9';
CREATE EXTERNLOGIN DBA TO diskserver9;
CREATE EXISTING TABLE diskdir9 AT 'diskserver9;;;.';

Send feedback about this page via email or DocCommentXchange