CREATE SERVER statement

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

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

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

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

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

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-string.
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-string 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 12;dsn=my_dsn'
If a JDBC-based server class is used, the USING clause is of the form host-name:port-number [/dbname], where:
- host-name The computer the remote server runs on.
- port-number 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 are READONLY and cannot be modified. By default, this is set to NO.
CREATEDIRS clause Specifies whether directories can be created using the directory access server. The default is NO.

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

Syntax 1 The CREATE SERVER statement defines a remote server.

The SAJDBC, ASEJDBC, and IQJDBC JDBC-based server classes are deprecated and should not be used.

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.

DBA authority

Not supported on Windows Mobile.

Automatic commit.

SQL/2008 Vendor extension.

The following example creates a SQL Anywhere remote server named testsa, using the SQL Anywhere ODBC driver.

CREATE SERVER testsa
CLASS 'SAODBC'
USING 'Driver=SQL Anywhere 12;DSN=remoteSA';

The following example creates an Adaptive Server Enterprise (ASE) remote server named ase_prod using the ASE ODBC driver.

CREATE SERVER ase_prod
CLASS 'ASEODBC'
USING 'DSN=remoteASE';

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;;;.';

CREATE SERVER statement

Syntax 1

Syntax 2

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example