ALTER SERVER statement

Use this statement to modify the attributes of a remote server.

Syntax 
ALTER SERVER server-name
[ CLASS server-class ]
[ USING connection-info ]
[ CAPABILITY cap-name { ON | OFF } ]
[ CONNECTION CLOSE [ CURRENT | ALL | connection-id ] ]

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

connection-info :
computer-name:port-number[/dbname ] | data-source-name
Parameters

  • CLASS clause   The CLASS clause is specified to change the server class.

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

  • USING clause   The USING clause is specified to change the server connection information. For information about connection-info, see CREATE SERVER statement.

  • CAPABILITY clause   The CAPABILITY clause turns 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 is 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 capabilities of a generic server of class ODBC.

  • CONNECTION CLOSE clause   When a user creates a connection to a remote server, the remote connection is not closed until the user disconnects from the local database. The CONNECTION CLOSE clause allows you to explicitly close connections to a remote server. You may find this useful when a remote connection becomes inactive or is no longer needed.

    The following SQL statements are equivalent and close the current connection to the remote server:

    ALTER SERVER server-name CONNECTION CLOSE;
    		 
    ALTER SERVER server-name CONNECTION CLOSE CURRENT;

    You can close both ODBC and JDBC connections to a remote server using this syntax. You do not need DBA authority to execute either of these statements.

    You can also disconnect a specific remote ODBC connection by specifying a connection ID, or disconnect all remote ODBC connections by specifying the ALL keyword. If you attempt to close a JDBC connection by specifying the connection ID or the ALL keyword, an error occurs. When the connection identified by connection-id is not the current local connection, the user must have DBA authority to be able to close the connection.

Remarks

The ALTER SERVER statement modifies the attributes of a server. These changes do not take effect until the next connection to the remote server.

Permissions

Must have RESOURCE authority.

Side effects

Automatic commit.

See also
Standards and compatibility

  • SQL/2003   Vendor extension.

Example

The following example changes the server class of the Adaptive Server Enterprise server named ase_prod so its connection to SQL Anywhere is ODBC-based. Its Data Source Name is ase_prod. 

ALTER SERVER ase_prod
CLASS 'ASEODBC'
USING 'ase_prod';

The following example changes a capability of server infodc.

ALTER SERVER infodc
CAPABILITY 'insert select' OFF;

The following example closes all connections to the remote server named rem_test.

ALTER SERVER rem_test
CONNECTION CLOSE ALL;

The following example closes the connection to the remote server named rem_test that has the connection ID 142536.

ALTER SERVER rem_test
CONNECTION CLOSE 142536;
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1