Changing a database to another collation requires a rebuild of the database. Collations are chosen at database creation time and cannot be changed.

To change collations

1. Determine the character set for the existing database as follows:

   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.

2. Determine the character set for the data in the existing database.

   This should be the same as, or compatible with, the database character set. If it is not, it is an excellent reason to rebuild the database, but requires great care in the rebuilding 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 expecting data to be in code page 850. A simple test for this case is to use UNLOAD TABLE with the ENCODING option to unload some character data, then view it in Windows Notepad. 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.

3. Unload the database.

   If the data character set is incompatible with the database character set, it is critical that the data be 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.

4. Create the new database, specifying the collations and character sets you want to use.

5. Load the data into the new database.

   If the unloaded data and schema (reload.sql) match the character set of the computer used to do the reload, you can use the external reload option of dbunload. The server's character set conversion will automatically convert the data to the correct character set for the database.

   If the data's encoding does not match the character set of the database, and you are loading 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.

See also

• LOAD TABLE statement
• UNLOAD statement
• INPUT statement [Interactive SQL]
• Creating a database with a named collation
• Unload utility (dbunload)
• Rebuilding databases
• CharSet connection parameter [CS]