ALTER SERVER statement

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' 
| 'IQODBC' 
| 'MSSODBC'
| 'ORAODBC' 
| 'MSACCESSODBC' 
| 'MYSQLODBC' 
| 'ULODBC' 
| 'ADSODBC' 
| 'ODBC' 
| 'SAJDBC' 
| 'ASEJDBC'
| 'IQJDBC'

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

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.

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

DBA authority.

Automatic commit.

SQL/2008 Vendor extension.

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;

ALTER SERVER statement

Syntax

Parameters

Remarks

Permissions

Side effects

See also

Standards and compatibility

Example