ASA Database Administration Guide
  Adaptive Server Anywhere as an Open Server

Characteristics of Open Client and jConnect connections

When Adaptive Server Anywhere is serving applications over TDS, it automatically sets relevant database options to values compatible with Adaptive Server Enterprise default behavior. These options are set temporarily, for the duration of the connection only. The client application can override them at any time.

Default settings 

The database options set on connection using TDS include:

How the startup options are set 

The default database options are set for TDS connections using a system procedure named sp_tsql_environment. This procedure sets the following options:

SET TEMPORARY OPTION TSQL_VARIABLES='ON';
SET TEMPORARY OPTION ANSI_BLANKS='ON';
SET TEMPORARY OPTION TSQL_HEX_CONSTANT='ON';
SET TEMPORARY OPTION CHAINED='OFF';
SET TEMPORARY OPTION QUOTED_IDENTIFIER='OFF';
SET TEMPORARY OPTION ALLOW_NULLS_BY_DEFAULT='OFF';
SET TEMPORARY OPTION AUTOMATIC_TIMESTAMP='ON';
SET TEMPORARY OPTION ANSINULL='OFF';
SET TEMPORARY OPTION CONTINUE_AFTER_RAISERROR='ON';
SET TEMPORARY OPTION FLOAT_AS_DOUBLE='ON';
SET TEMPORARY OPTION ISOLATION_LEVEL='1';
SET TEMPORARY OPTION DATE_FORMAT='YYYY-MM-DD';
SET TEMPORARY OPTION TIMESTAMP_FORMAT='YYYY-MM-DD HH:NN:SS.SSS';
SET TEMPORARY OPTION TIME_FORMAT='HH:NN:SS.SSS';
SET TEMPORARY OPTION DATE_ORDER='MDY';
SET TEMPORARY OPTION ESCAPE_CHARACTER='OFF'

The procedure sets options only for connections that use the TDS communications protocol. This includes Open Client and JDBC connections using jConnect. Other connections (ODBC and Embedded SQL) have the default settings for the database.

You can change the options for TDS connections.

To change the option settings for TDS connections

1. Create a procedure that sets the database options you want. For example, you could use a procedure such as the following:

   CREATE PROCEDURE my_startup_procedure()
   BEGIN
     IF connection_property('CommProtocol')='TDS' THEN
       SET TEMPORARY OPTION QUOTED_IDENTIFIER='OFF';
     END IF
   END

   This particular procedure example changes only the QUOTED_IDENTIFIER option from the default setting.

2. Set the LOGIN_PROCEDURE option to the name of a new procedure:

   SET OPTION LOGIN_PROCEDURE= 'DBA.my_startup_procedure'

Future connections will use the procedure. You can configure the procedure differently for different user IDs.

For more information about database options, see Database Options.