Characteristics of Open Client and jConnect connections

When SQL 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:

Option

Set to

allow_nulls_by_default

Off

ansi_blanks

On

ansinull

Off

chained

Off

close_on_endtrans

Off

date_format

YYYY-MM-DD

date_order

MDY

escape_character

Off

isolation_level

1

on_tsql_error

Continue

quoted_identifier

Off

time_format

HH:NN:SS.SSS

timestamp_format

YYYY-MM-DD HH:NN:SS.SSS

tsql_variables

On

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:

Do not edit the sp_tsql_environment procedure

Do not alter the sp_tsql_environment procedure yourself. It is for system use only.

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

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.

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.