Chinese characters in an upgraded NAV 2017 database

lzr

We have had chinese characters in our NAV 2009 database running the classic client and after upgrading using the standard Microsoft toolkits these are shown incorrectly in NAV 2017. Does anyone have any experience regarding this? Is there a tool you could run afterwards?

Slawek_Guzek

Hi

English language uses first 127 codes of ascii, as far as I am aware these stay invariant regargless of the code page or collation used. If you have only English + Chinese languages in your database it should be safe to use different code page for conversion.

You can fix the data using SQL queries after conversion. This is not officialy supported way, but possible. The trouble with this approach is to build a query, or rather a series of SQL queries converting the data. Many tables/many fields and many characters to replace - and the REPLACE function in SQL can address just one character pair at a time.

If you wait for SQL 2017 to be released, and upgrade your SQL Server to 2017, you will have TRANSLATE function in there, working much like CONVERTSTR in NAV, but I guess that's not very helpful for you

You could also write some SQL queries to create a copy of your tables in SQL, and make copies while the databas is in 2009 version, before conversion. These tables would not get converted into unicode, NAV ignores anything non-NAV in the database. Then after conversion you could copy affected columns across, manually specyfying covnersion collation. Still not ideal an there will be a lots of SQL code to write, but I quess less tedious that writting tons of REPLACE functions

Slawek

Slawek_Guzek

Hi,

The text conversion takes place when your 2009 database gets converted to 2013 (or later - depending what steps you are taking). This is when your non-unicode data gets converted into unicode. This is all done by the SQL server and NAV code/MS upgrade toolkit has nothig to with it.

You may want to check/play with the 2009 database collation settings before upgrading it, looking at default SQL Server collation, or try with different localisation setting on the PC used to run this particular. Not sure if it helps but worth looking at.

Slawek

lzr

Hi and thanks for the input. The problem is that all other companies are English and almost all data except a few fields in a few tables are English. I guess I could accomplish the conversion using a computer with chinese locale and regional settings. I am a bit afraid to do this though since it might affect other things around it. Would it be possible to do this with SQL operations afterwards or is it "too late"?

Slawek_Guzek

Hi

English language uses first 127 codes of ascii, as far as I am aware these stay invariant regargless of the code page or collation used. If you have only English + Chinese languages in your database it should be safe to use different code page for conversion.

You can fix the data using SQL queries after conversion. This is not officialy supported way, but possible. The trouble with this approach is to build a query, or rather a series of SQL queries converting the data. Many tables/many fields and many characters to replace - and the REPLACE function in SQL can address just one character pair at a time.

If you wait for SQL 2017 to be released, and upgrade your SQL Server to 2017, you will have TRANSLATE function in there, working much like CONVERTSTR in NAV, but I guess that's not very helpful for you

You could also write some SQL queries to create a copy of your tables in SQL, and make copies while the databas is in 2009 version, before conversion. These tables would not get converted into unicode, NAV ignores anything non-NAV in the database. Then after conversion you could copy affected columns across, manually specyfying covnersion collation. Still not ideal an there will be a lots of SQL code to write, but I quess less tedious that writting tons of REPLACE functions

Slawek

lzr

Thanks a lot for your help. I might create a new table as you say with nvarchar fields and then convert the fields using cast from the old varchar fields to the new ones. There are not so many table/fields to convert, the use of Chinese characters is pretty limited.

I found this solution where the answer gives a nice example
https://dba.stackexchange.com/questions/41608/converting-non-unicode-string-to-unicode-string-ssis