Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP Sybase SQL Anywhere 16.0 » SQL Anywhere Server - Database Administration » Database configuration » International languages and character sets » Collations


Changing the database collation

You can change your database collation from one collation to another by performing an unload and reload into the collation of choice. Collations are chosen at database creation time and cannot be changed without rebuilding the database.


You must have the SELECT ANY TABLE and SERVER OPERATOR system privileges.

By default, you must have the SELECT ANY TABLE system privilege to execute an UNLOAD statement. The required privileges can be changed by using the -gl database server option.

  1. Start the database:

    START databasefile 
  2. Determine the character set for the existing database:

    SELECT DB_PROPERTY( 'CharSet' );

    For early versions of SQL Anywhere, this property may not exist. The character set is also implied by the collation name. For example, collation 1252LATIN1 uses code page 1252.

    The character set in the existing database should be the same as, or compatible with, the operating system and client character sets. If it is not, it is an excellent reason to rebuild the database, but requires great care in the rebuild process.

    In particular, if you have been using a database with collation 850LATIN1 with earlier versions of SQL Anywhere that either did not support character set conversion (versions 5 and earlier) or disabled it by default (versions 6 and 7), and if your client applications were normal Windows applications, you may have code page 1252 character data in your database that is interpreting data to be in code page 850.

  3. Determine the character set for the data in the existing database:

    UNLOAD TABLE mytable TO 'mytable-data-in-utf8.dat' ENCODING 'UTF-8';

    View the results in a text editor.

    If accented data is correct, then the character data in the database matches the Windows ANSI code page, which for English and other Western European languages is code page 1252. If the data appears correct in a DOS-based editor, then the character data matches the Windows OEM code page, which is likely 437 or 850.

  4. Unload the database.

    If the data character set is incompatible with the database character set, it is critical that the data is unloaded without character set conversion. Depending on the version of SQL Anywhere being used, you can use the internal unload feature of dbunload, or manually unload the data using the UNLOAD TABLE statement.

  5. Create the new database, specifying the collations and character sets you want to use, and set the DBA user ID and password to DBA and sql:

    dbinit -dba DBA,sql -z 1252LATIN1 c:\newdatabase.db 
  6. Stop the old database server and start the new database server:

    dbsrv16 -n new-server c:\newdatabase.db 
  7. Load the data into the new database.

    If the unloaded data and schema (reload.sql) match the character set of the computer used to perform the reload, you can use the external reload option of dbunload. The data is automatically converted to the correct character set for the database. For example:

    dbunload -ix c:\databasefile

    If the data's encoding does not match the character set of the database, and you are loading the data using LOAD TABLE statements (internal reload), you must use the ENCODING clause; the database server does not, by default, perform character set conversion for data loaded using LOAD TABLE statements.

    If the data's encoding does not match the code page of the computer on which you are working, and you are loading using INPUT statements (external reload), you must use the ENCODING clause; otherwise, the database server assumes that the data is in the computer's native character set.


The database is recreated with the new collation.


For example, suppose you have a database with collation ISO1LATIN1 but discovered you need to support the euro currency symbol. Since ISO1LATIN1 does not support the euro symbol, but other European character sets such as 1252LATIN1 do, you can unload your database, create a new one with 1252LATIN1, and reload your database.

See [external link] Changing the Collation and Codepage When Rebuilding a SQL Anywhere Database .

 See also