CREATE SERVER statement

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

server-class-string :
'ADSODBC' 
| 'ASEODBC' 
| 'DB2ODBC' 
| 'HANAODBC' 
| 'IQODBC' 
| 'MIRROR' 
| 'MSACCESSODBC' 
| 'MSSODBC'
| 'MYSQLODBC' 
| 'ODBC'
| 'ORAODBC' 
| 'SAODBC' 
| 'ULODBC'

connection-info-string :
  { '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 } ] [ ;DELIMITER = { / | \ } ]'

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.

If disk sandboxing is enabled, then the database's operations are limited to the directory where the main database file is located. See Disk sandboxing.
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.
The USING clause is an ODBC connection string that can include 'DSN=data-source-name' to specify an ODBC data source name and/or 'DRIVER=driver-name' to specify a driver binary on Unix or driver name on Windows.

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.

The string in the USING clause can also contain local or global variable names enclosed in braces ({variable-name}). The SQL variable name must be of type CHAR, VARCHAR, or LONG VARCHAR. For example, a USING clause that contains 'DSN={@mydsn}' indicates that @mydsn is a SQL variable and that the current contents of the @mydsn variable should be substituted when a connection is made to the remote data access server.

In Syntax 2, the USING clause specifies the following values for the local directory:
- ROOT clause Specifies 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.
  If disk sandboxing is enabled, then the database's operations are limited to the directory where the main database file is located. See Disk sandboxing.
- SUBDIRS clause Specifies 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, READONLY is set to NO.
- CREATEDIRS clause Specifies whether directories can be created using the directory access server. The default is NO.
- DELIMITER clause Specifies whether paths in the file_name column are delimited by a slash (/) or backslash (\) character. By default, the native path delimiter is used.
The string in the USING clause can also contain local or global variable names enclosed in braces ({variable-name}). The SQL variable name must be of type CHAR, VARCHAR, or LONG VARCHAR. For example, a USING clause that contains 'ROOT={@mypath}' indicates that @mypath is a SQL variable and that the current contents of the @mypath variable should be substituted when a connection to the directory access server is established. This permits the creation of dynamic directory access servers.

For more information about using variables in the USING clause, see Creating directory access servers (SQL).
READ ONLY clause Specifies that the remote server is accessed in read-only mode.

This statement is not supported on Windows Mobile.

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

Syntax 1 The CREATE SERVER statement defines a remote server.

To bypass the ODBC driver manager when defining a SQL Anywhere remote server, use the syntax below, followed by the remainder of the connection-info-string:

CREATE SERVER remote-server
CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere Native;DSN=my-dsn;UID=my-username;PWD=my-pwd';

This syntax allows remote data access to load the SQL Anywhere ODBC driver directly and is supported by Windows and Unix. Loading the SQL Anywhere ODBC driver directly ensures that the ODBC driver for the current server version is used. Also, if the SQL Anywhere ODBC driver is only used for remote data access, it does not need to be registered.

Note

If the application also makes use of non-SQL Anywhere remote servers, or if there are SQL Anywhere remote servers defined without using 'DRIVER=SQL Anywhere Native', then remote data access still uses a driver manager for the other remote servers.

On Unix platforms you can also reference the SQL Anywhere ODBC driver. The syntax is as follows:

USING 'DRIVER=SQL Anywhere 16;DSN=my-dsn'

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 privileges granted to the database server user.

You must have the SERVER OPERATOR system privilege.

Automatic commit.

SQL/2008 Vendor extension.

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

CREATE SERVER RemoteSA
CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere 16;DSN=RemoteDS';

The following example directly loads the SQL Anywhere ODBC driver without using the ODBC driver manager:

CREATE SERVER RemoteSA
CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere Native;DSN=RemoteDS';

The following example uses a variable reference to create a dynamic remote data access server. You need the MANAGE ANY USER and CREATE TABLE system privileges to run this example.

CREATE SERVER RemoteSA
CLASS 'SAODBC'
USING 'DRIVER=SQL Anywhere 16;DSN={dsn_string};Server=saremote;UID=DBA;PWD=sql';

CREATE VARIABLE dsn_string LONG VARCHAR;
SET dsn_string = 'Test16';

CREATE EXTERNLOGIN DBA TO RemoteSA;

CREATE EXISTING TABLE test_employees
AT 'RemoteSA...Employees';
SELECT * FROM test_employees;
DROP REMOTE CONNECTION TO RemoteSA CLOSE ALL;

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 privileges, file_name, size FROM diskdir0;

The following example creates a dynamic directory access server that is used to explore two different directories.

CREATE SERVER diskserver9
CLASS 'DIRECTORY'
USING '{dir_options}';

CREATE EXTERNLOGIN DBA TO diskserver9;
CREATE EXISTING TABLE diskdir9 AT 'diskserver9;;;.';

CREATE VARIABLE dir_options VARCHAR(256);
SET dir_options = 'ROOT=c:\\temp;SUBDIRS=9;DELIMITER=/';
SELECT * FROM diskdir9;

DROP REMOTE CONNECTION TO diskserver9 CLOSE ALL;
SET dir_options = 'ROOT=c:\\ProgramData;SUBDIRS=9;DELIMITER=/';
SELECT * FROM diskdir9;

CREATE SERVER statement

Syntax 1

Syntax 2

Parameters

Remarks

Note

Privileges

Side effects

See also

Standards and compatibility

Example