CREATE SERVER statement

Creates a remote server or a directory access server.

Syntax

Create a remote server

CREATE [ REMOTE ] SERVER server-name
CLASS server-class-string | variable
USING connection-info-string | variable
[ READ ONLY [ ON | OFF | VALUE variable ] ]
[ DEFAULT LOGIN string | variable [ IDENTIFIED BY string | variable ] ]

server-class-string : 
{ 'ADSODBC' | 'ADS_ODBC'  
| 'ASEODBC' | 'ASE_ODBC' 
| 'DB2ODBC' | 'DB2_ODBC' 
| 'HANAODBC' | 'HANA_ODBC' 
| 'IQODBC' | 'IQ_ODBC' 
| 'MIRROR' 
| 'MSACCESSODBC' | 'MSACCESS_ODBC' 
| 'MSSODBC' | 'MSS_ODBC' 
| 'MYSQLODBC' | 'MYSQL_ODBC' 
| 'ODBC'
| 'ORAODBC' | 'ORA_ODBC' 
| 'SAODBC' | 'SA_ODBC' 
| 'ULODBC' | 'UL_ODBC' }

connection-info-string :
  { 'data-source-name' | 'sqlanywhere-connection-string' }

Create a directory access server

CREATE SERVER server-name
CLASS 'DIRECTORY'
USING using-string | variable
[ READ ONLY [ ON | OFF | VALUE variable ] ]
[ ALLOW { 'ALL' | 'SPECIFIC' | variable  } USERS ]

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

Create a remote server (SAP HANA syntax)

CREATE REMOTE SOURCE remote-source-name
   ADAPTER adapter-name | variable
   CONFIGURATION connection-info-string | variable
   [ READ ONLY [ ON | OFF | VALUE variable ] ]
   [ WITH CREDENTIAL TYPE { 'PASSWORD' | variable } USING { 'USER=remote-user;password=remote-password' | variable ]

Parameters

CREATE [ REMOTE ] SERVER
The REMOTE keyword is for remote servers, is optional, and is provided for compatibility with other databases.
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 to create a directory access server that accesses a directory on the local computer.

The server classes are:
- SAODBC
  for SQL Anywhere.
- ULODBC
  for UltraLite.
  
  Note You cannot create a remote server for an UltraLite database running on Mac OS X.
- ADSODBC
  for SAP Advantage Database Server.
- ASEODBC
  for SAP Adaptive Server Enterprise (version 10 and later).
- DB2ODBC
  for IBM DB2.
- HANAODBC
  for SAP HANA.
- IQODBC
  for SAP IQ.
- MSACCESSODBC
  for Microsoft Access.
- MSSODBC
  for Microsoft SQL Server.
- MYSQLODBC
  for Oracle MySQL.
- ODBC
  for all other ODBC data sources.
- ORAODBC
  for Oracle Database servers (version 8.0 and later).
Note When using remote data access, if you use an ODBC driver that does not support Unicode, then character set conversion is not performed on data coming from that ODBC driver.
READ ONLY clause (remote server)
Specifies that the remote server is accessed in read-only mode. If the clause is not specified, or if READ ONLY OFF is specified, then the remote server is not accessed in read-only mode. If READ ONLY or READ ONLY ON is specified, then the remote server is accessed in read-only mode.
READ ONLY clause (directory access server)
Specifies whether the files accessed by the directory are read-only and cannot be modified. By default, READ ONLY is set to NO.
ALLOW USERS clause (directory access servers)
Specify ALLOW 'SPECIFIC' USERS to restrict access to the directory access server to users with an external login. You must explicitly create an external login to the directory access server for each user that needs access. This clause is the default.

Specify the ALLOW 'ALL' USERS clause if you are not concerned about who has access to the directory access server, or you want everyone in your database to have access. This clause creates a default external login for the directory access server that is available to all users.
USING clause (remote servers)
When you create a remote server, 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 value specified.

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 a driver name on Windows.

For SQL Anywhere remote servers (SAODBC server classes), the connection-info-string parameter can be any valid connection string. Use any supported connection parameter. For example, if you have connection problems, then 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.
USING clause (directory access servers)
Specify the server connection information.

When you create a directory access server, 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.
- 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.
- CREATEDIRS clause
  Specifies whether directories can be created using the directory access server. The default is NO.
- DELIMITER clause
  Specifies whether paths 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.
DEFAULT LOGIN clause (remote server)
Specifies a default user ID and (optionally) password for an account on the remote server that is to be used the default login. Values for the DEFAULT LOGIN clause are restricted to 128 bytes.
- IDENTIFIED BY clause
  The IDENTIFIED BY clause specifies the remote password for the remote user. This value can be either a string or a variable. The remote user and remote password combination must be valid on the remote-server.
  
  If you omit the IDENTIFIED BY clause, the password is sent to the remote server as NULL. However, if you specify IDENTIFIED BY "" (an empty string), then the password sent is the empty string.
  
  If you use this statement in a procedure, do not specify the password (IDENTIFIED BY clause) as a string literal because the definition of the procedure is visible in the SYSPROCEDURE system view. For security purposes, specify the password using a variable that is declared outside of the procedure definition.
CREATE REMOTE SOURCE (SAP HANA syntax)
This syntax, including the parameters and their values, is semantically equivalent to the syntax for creating a remote server (CREATE [ REMOTE ]) syntax and is provided for compatibility with SAP HANA servers. There is a one-to-one clause match between the two syntaxes as follows:
- ADAPTER adapter-name
  See the CLASS clause description for the CREATE [ REMOTE ] SERVER syntax.
- CONFIGURATION connection-info-string
  See the USING clause description for the CREATE [ REMOTE ] SERVER syntax.
- READ ONLY [ ON | OFF ] clause
  See the READ ONLY clause description for the CREATE [ REMOTE ] SERVER syntax (remote servers).
- WITH CREDENTIAL TYPE clause
  See the DEFAULT LOGIN clause description for the CREATE [ REMOTE ] SERVER syntax.

Remarks (remote server)

Use the CREATE SERVER statement to create a remote server to access to data in other data sources. Once you create a remote server, you must create local proxy tables to map to the remote tables. Database users use proxy tables to access the contents of the remote tables. Create an external login for each database user that needs to communicate with the remote server.

Connections to a remote server are first attempted using the current executing user's external login. If this user does not have an external login, then the connection is attempted using the DEFAULT LOGIN credentials. If the remote server was created without a DEFAULT LOGIN, and no external login has been defined for the user, then the connection is attempted with the current executing user ID and password.

When running procedures that involve connections to a remote server, you can use the extern_login_credentials option to specify whether the remote data access connections are performed using the external login credentials of the logged in user or the effective user.

On Unix, the database server runs as a specific user, so file permissions are based on the privileges granted to the database server user.

When accessing data from a remote server, if you use an ODBC driver that does not support Unicode, then character set conversion is not performed on data coming from that ODBC drive

When you define a remote server, an entry is added to the ISYSSERVER system table for the remote server. View the list of remote servers by querying the SYSSERVER system view.

Note For required parameters that accept variable names, the database server returns an error if any of the following conditions is true:

The variable does not exist
The contents of the variable are NULL
The variable exceeds the length allowed by the parameter
The data type of the variable does not match that required by the parameter

Remarks (directory access server)

Use the CREATE SERVER statement to create a directory access server that accesses the local directory structure on the computer where the database server is running. Create an external login for each database user that needs to use the directory access server. Once you create a directory access server, you must create a proxy table for it. Database users use proxy tables to access the contents of a directory on the database server's local file system.

View the list of directory access servers by querying the SYSSERVER system view.

Privileges

You must have the SERVER OPERATOR system privilege.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

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 17;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 17;DSN={dsn_string};Server=saremote;UID=DBA;PWD=sql';

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

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;

When you create a remote server, to bypass the ODBC driver manager, 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, then it does not need to be registered.

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

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

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.