ALTER SERVER statement

Modifies the attributes of a remote server.

Syntax

Alter a remote server

ALTER [ REMOTE ] SERVER server-name
[ CLASS server-class | variable ]
[ USING connection-string-info | variable ]
[ CAPABILITY cap-name { ON | OFF | VALUE variable } ]
[ READ ONLY [ ON | OFF | VALUE variable ] ]
[ DEFAULT LOGIN string | variable [ IDENTIFIED BY string | variable ]  | NO DEFAULT LOGIN ]

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' }

Alter a directory access server

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

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

Alter a remote server (SAP HANA syntax)

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

Parameters

ALTER [ REMOTE ] SERVER
The REMOTE keyword is optional when altering a remote server and is provided for compatibility with other databases.
CLASS clause Specify this clause to change the server class.
USING clause
Specify the server connection information.

The string in the USING clause can 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 value of the @mydsn variable is substituted when a connection is made to the remote data access server.
CAPABILITY clause
Specify this clause to turn a server capability ON or OFF. Server capabilities are stored in the ISYSCAPABILITY system table. The names of these capabilities are accessible via the SYSCAPABILITYNAME system view. The ISYSCAPABILITY system table and SYSCAPABILITYNAME system view are not populated with data until the first connection to a remote server is made. For subsequent connections, the database server's capabilities are obtained from the ISYSCAPABILITY system table.

In general, you do not need to alter a server's capabilities. It may be necessary to alter the capabilities of a generic server of class ODBC.
READ ONLY clause (remote server)
Specifies whether the remote server is accessed in read-only mode.

If the READ ONLY clause is not specified, then the read-only setting of the server remains unaffected. If READ ONLY or READ ONLY ON is specified, then the server is changed to be read only. If READ ONLY OFF is specified, then the server is changed to be read-write.
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
Specifies whether users can use the directory access server without requiring an external login (externlogin). Specifying the ALLOW 'ALL' USERS clause allows you to create or alter directory access servers to no longer require external logins for each user. Specifying the ALLOW 'SPECIFIC' USERS is equivalent to requiring an external login for each user that uses the directory access server. Not specifying this clause is equivalent to specifying ALLOW 'SPECIFIC' USERS.
DEFAULT LOGIN clause
Specify DEFAULT LOGIN to add or change the default login for the remote server. Specifying NO DEFAULT LOGIN removes the default login for the remote server, if one exists.
ALTER REMOTE SOURCE (SAP HANA syntax)
This syntax, including the parameters and their values, is semantically equivalent to the syntax for altering 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 ALTER [ REMOTE ] SERVER syntax.
- CONFIGURATION connection-info-string
  See the USING clause description for the ALTER [ REMOTE ] SERVER syntax.
- CAPABILITY cap-name
  See the CAPABILITY clause description for the ALTER [ REMOTE ] SERVER syntax.
- READ ONLY [ ON | OFF ] clause
  See the READ ONLY clause description for the ALTER [ REMOTE ] SERVER syntax (remote server).
- WITH CREDENTIAL TYPE clause
  See the DEFAULT LOGIN clause description for the ALTER [ REMOTE ] SERVER syntax. If you are using variables in the WITH CREDENTIAL TYPE clause, then the variable must be a string containing the value 'PASSWORD' and the USING variable must be string using the 'user=...;password=...' format.

Remarks

Changes do not take effect until the next connection to the remote server.

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.

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

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 changes the DSN of the Adaptive Server Enterprise server named ase_prod to ase_datasource.

ALTER SERVER ase_prod
    USING 'ase_datasource';

The following example changes the DSN of the Adaptive Server Enterprise server named ase_prod such that its data source name is obtained from the variable ase_source.

ALTER SERVER ase_prod
    USING '{ase_source}';

CREATE VARIABLE ase_source VARCHAR(128);
    SET ase_source = 'ase_datasource';

The following example changes a capability of a server ase_prod.

ALTER SERVER ase_prod
    CAPABILITY 'insert select' OFF;

The following example alters a directory access server so that it retrieves nine levels of subdirectories within the directory c:\temp.

ALTER SERVER ase_prod
    CLASS 'DIRECTORY'
    USING 'ROOT=c:\\temp;SUBDIRS=9';