When entering text in non-english languages into Emerald the data entered is being replaced with question marks. (?????) What is causing this and how can I fix it?
May 23 2007
The following translation table provides the Microsoft SQL server collation types used for each country in the regional settings.
Afrikaans - Latin1_General_CI_AS
Albanian - Albanian_CI_AS
Arabic (Algeria) - Arabic_CI_AS
Arabic (Bahrain) - Arabic_CI_AS
Arabic (Egypt) - Arabic_CI_AS
Arabic (Iraq) - Arabic_CI_AS
Arabic (Jordan) - Arabic_CI_AS
Arabic (Kuwait) - Arabic_CI_AS
Arabic (Lebanon) - Arabic_CI_AS
Arabic (Libya) - Arabic_CI_AS
Arabic (Morocco) - Arabic_CI_AS
Arabic (Oman) - Arabic_CI_AS
Arabic (Qatar) - Arabic_CI_AS
Arabic (Saudi Arabia) - Arabic_CI_AS
Arabic (Syria) - Arabic_CI_AS
Arabic (Tunisia) - Arabic_CI_AS
Arabic (U.A.E.) - Arabic_CI_AS
Arabic (Yemen) - Arabic_CI_AS
Armenian (Republic of Armenia) - Latin1_General_CI_AS
Uzbek-Cyrillic (Republic of Uzbeckistan) - Cyrillic_General_CI_AS
Vietnamese - Vietnamese_CI_AS
May 23 2007
Microsofts ODBC driver forces character set translation *even if the option is disabled in the ODBC control panel* when the computers locale is not compatible with the collation type of the Emerald database.
Since the database server does not nativly understand the UTF8 character encodings used by Emerald the data is incorrectly converted by the MS ODBC driver causing non-english character data to be incorrectly stored in the database.
To prevent automatic conversion the Emerald systems regional settings must be compatible with the Emerald databases collation type. You can verify this is the case by using the windows odbc control panel (odbccp32.cpl) to test the Emerald datasource. If the test fails with a message 'perform translation for character data should be checked' there is a mismatch.
Also the 'Perform translation for character data' configuration option for your Emerald ODBC datasource *must not be enabled*.
There only way to resolve a mismatch with an existing Emerald database is by changing the language in the computers regional settings.
To obtain the collation type of the Emerald database on the Microsoft SQL platform use SQL Enterprise manager/management studio or run the following query: SELECT databasepropertyex('Emerald5', 'Collation')
Under Windows 2003/XP the operating systems regional setting is changed from the control panel / Regional And Language Options / Advanced / Language via the selection list for non-unicode programs.
Please see your database servers documentation on the correct language to use based on collation type used for the Emerald systems regional settings.
NOTE: you *MUST NOT* alter the collation type of the database. Doing this does not change the collation type of existing objects within the database and will cause compatibility problems with future updates creating objects with a different collation type from their peers.
