ASA SQL User's Guide
  Transact-SQL Compatibility
    Configuring databases for Transact-SQL compatibility

Setting options for Transact-SQL compatibility

You set Adaptive Server Anywhere database options using the SET OPTION statement. Several database option settings are relevant to Transact-SQL behavior.

Set the allow_nulls_by_default option 

By default, Adaptive Server Enterprise disallows NULLs on new columns unless you explicitly tell the column to allow NULLs. Adaptive Server Anywhere permits NULL in new columns by default, which is compatible with the SQL/92 ISO standard.

To make Adaptive Server Enterprise behave in a SQL/92-compatible manner, use the sp_dboption system procedure to set the allow_nulls_by_default option to true.

To make Adaptive Server Anywhere behave in a Transact-SQL-compatible manner, set the allow_nulls_by_default option to OFF. You can do this using the SET OPTION statement as follows:

SET OPTION PUBLIC.allow_nulls_by_default = 'OFF'

Set the quoted_identifier option 

By default, Adaptive Server Enterprise treats identifiers and strings differently than Adaptive Server Anywhere, which matches the SQL/92 ISO standard.

The quoted_identifier option is available in both Adaptive Server Enterprise and Adaptive Server Anywhere. Ensure the option is set to the same value in both databases, for identifiers and strings to be treated in a compatible manner.

For SQL/92 behavior, set the quoted_identifier option to ON in both Adaptive Server Enterprise and Adaptive Server Anywhere.

For Transact-SQL behavior, set the quoted_identifier option to OFF in both Adaptive Server Enterprise and Adaptive Server Anywhere. If you choose this, you can no longer use identifiers that are the same as keywords, enclosed in double quotes.

For more information on the quoted_identifier option, see QUOTED_IDENTIFIER option [compatibility].

Set the automatic_ timestamp option to ON 

Transact-SQL defines a timestamp column with special properties. With the automatic_timestamp option set to ON, the Adaptive Server Anywhere treatment of timestamp columns is similar to Adaptive Server Enterprise behavior.

With the automatic_timestamp option set to ON in Adaptive Server Anywhere (the default setting is OFF), any new columns with the TIMESTAMP data type that do not have an explicit default value defined receive a default value of timestamp.

For information on timestamp columns, see The special Transact-SQL timestamp column and data type.

Set the string_rtruncation option 

Both Adaptive Server Enterprise and Adaptive Server Anywhere support the string_rtruncation option, which affects error message reporting when an INSERT or UPDATE string is truncated. Ensure that each database has the option set to the same value.

For more information on the STRING_RTRUNCATION option, see STRING_RTRUNCATION option [compatibility].

For more information on database options for Transact-SQL compatibility, see Compatibility options.