Contents Index Configuring databases for Transact-SQL compatibility Setting options for Transact-SQL compatibility

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

Creating a Transact-SQL-compatible database


This section describes choices you must make when creating or rebuilding a database.

Quick start 

Here are the steps you need to take to create a Transact-SQL-compatible database. The remainder of the section describes which options you need to set.

To create a Transact-SQL compatible database (Sybase Central)

  1. Start Sybase Central.

  2. Choose Tools > Adaptive Server Anywhere 9 > Create Database.

  3. Follow the instructions in the wizard.

  4. When you see a button called Emulate Adaptive Server Enterprise, click it, and then click Next.

  5. Follow the remaining instructions in the wizard.

To create a Transact-SQL compatible database (Command line)

To create a Transact-SQL compatible database (SQL)

  1. Connect to any Adaptive Server Anywhere database.

  2. Enter the following statement, for example, in Interactive SQL:

    CREATE DATABASE 'db-name.db'
    ASE COMPATIBLE

    In this statement, ASE COMPATIBLE means compatible with Adaptive Server Enterprise.

Make the database case sensitive 

By default, string comparisons in Adaptive Server Enterprise databases are case sensitive, while those in Adaptive Server Anywhere are case insensitive.

When building an Adaptive Server Enterprise-compatible database using Adaptive Server Anywhere, choose the case sensitive option.

Ignore trailing blanks in comparisons 

When building an Adaptive Server Enterprise-compatible database using Adaptive Server Anywhere, choose the option to ignore trailing blanks in comparisons.

When you choose this option, Adaptive Server Enterprise and Adaptive Server Anywhere considers the following two strings equal:

'ignore the trailing blanks   '
'ignore the trailing blanks'

If you do not choose this option, Adaptive Server Anywhere considers the two strings above different.

A side effect of this choosing this option is that strings are padded with blanks when fetched by a client application.

Remove historical system views 

Older versions of Adaptive Server Anywhere employed two system views whose names conflict with the Adaptive Server Enterprise system views provided for compatibility. These views include SYSCOLUMNS and SYSINDEXES. If you are using Open Client or JDBC interfaces, create your database excluding these views. You can do this with the dbinit -k command-line switch.

If you do not use this option when creating your database, the following two statements return different results:

SELECT * FROM SYSCOLUMNS ;

SELECT * FROM dbo.syscolumns ;

To drop the Adaptive Server Anywhere system views from an existing database

  1. Connect to the database as a user with DBA authority.

  2. Execute the following statements:

    DROP VIEW SYS.SYSCOLUMNS ;
    DROP VIEW SYS.SYSINDEXES

    Caution    Ensure that you do not drop the dbo.syscolumns or dbo.sysindexes system view.


Contents Index Configuring databases for Transact-SQL compatibility Setting options for Transact-SQL compatibility