Chinese characters in an upgraded NAV 2017 database

Answers

• Options

  Slawek_Guzek Member Posts: 1,690

  2017-09-18

  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
  
  

  Slawek Guzek
  Dynamics NAV, MS SQL Server, Wherescape RED;
  PRINCE2 Practitioner - License GR657010572SG
  GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03

  0
• Options

  lzr Member Posts: 264

  2017-09-18

  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"?

  Navision developer

  0
• Options

  Slawek_Guzek Member Posts: 1,690

  2017-09-18 edited 2017-09-18 Answer ✓

  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
  Dynamics NAV, MS SQL Server, Wherescape RED;
  PRINCE2 Practitioner - License GR657010572SG
  GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03

  1
• Options

  lzr Member Posts: 264

  2017-09-19

  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
  

  Navision developer

  0