China NAV 2009 to NAV 2015

vremeni4

Hi Guys,

I successfully upgrade a NAV2009 Database to NAV 2015 CU15.
So far all is working fine, even the Chinese language works fine in RTC. (language pack installed)

Except the data in the tables.
If I open e.g. Customer table in the field "Name", I see gibberish instead of Chinese letters.
If I copy the gibberish text back into "Name" field in NAV 2009 then Chinese letters appear.
If I copy Chines letters from the field "Name" (NAV 2009 database) into the field "Name" (NAV 20015 database ) again gibberish appears.

Both databases NAV 2009 and NAV 2015 use the same collation Latin1_General.

So it looks like some kind of conversion is done, but I am not sure what needs to be done to fix this.
(Double Character bytes is the issue, I assume )

Does anyone has any experience with this issue and can suggest something how to fix it.

Thanks for your help.

vremeni4

Considering that no one knows how to convert Double Byte characters to Unicode, here is a SQL script for Customer table.
Hope this will help someone in an upgrade.

--First you create a temp table where you set the right collation for fields
CREATE TABLE #NonUnicode
(No_ VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
Name VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Search Name] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Name 2] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
Address VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Address 2] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
City VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
Contact VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Address 3] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Name Change Name 2] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
Description VARCHAR(250) COLLATE Chinese_PRC_CI_AS
)

-- then you insert the records that you want to convert and you convert them by using --VARBINARY(8000).

INSERT INTO #NonUnicode(No_, Name, [Search Name], [Name 2], Address, [Address 2], City, Contact, [Address 3], [Name Change Name 2])
SELECT
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), No_ COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250),Name COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250),[Search Name] COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), [Name 2] COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), Address COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), [Address 2] COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), City COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), Contact COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), [Address 3] COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), [Name Change Name 2] COLLATE Latin1_General_CI_AI))
FROM [Cronus$Customer]

-- now you use the converted data from the temp table #NonUnicode to update the Customer table in NAV
UPDATE [Cronus$Customer]
SET Name = #NonUnicode.Name,
[Search Name] = #NonUnicode.[Search Name],
[Name 2] = #NonUnicode.[Name 2],
Address = #NonUnicode.Address,
[Address 2] = #NonUnicode.[Address 2],
City = #NonUnicode.City,
Contact = #NonUnicode.Contact,
[Address 3] = #NonUnicode.[Address 3],
[Name Change Name 2] = #NonUnicode.[Name Change Name 2]
FROM [Cronus$Customer]
INNER JOIN #NonUnicode ON [Cronus$Customer].No_ COLLATE Latin1_General_CI_AI = #NonUnicode.No_


That's all folks.

vremeni4

Considering that no one knows how to convert Double Byte characters to Unicode, here is a SQL script for Customer table.
Hope this will help someone in an upgrade.

--First you create a temp table where you set the right collation for fields
CREATE TABLE #NonUnicode
(No_ VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
Name VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Search Name] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Name 2] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
Address VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Address 2] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
City VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
Contact VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Address 3] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
[Name Change Name 2] VARCHAR(250) COLLATE Chinese_PRC_CI_AS,
Description VARCHAR(250) COLLATE Chinese_PRC_CI_AS
)

-- then you insert the records that you want to convert and you convert them by using --VARBINARY(8000).

INSERT INTO #NonUnicode(No_, Name, [Search Name], [Name 2], Address, [Address 2], City, Contact, [Address 3], [Name Change Name 2])
SELECT
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), No_ COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250),Name COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250),[Search Name] COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), [Name 2] COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), Address COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), [Address 2] COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), City COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), Contact COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), [Address 3] COLLATE Latin1_General_CI_AI)),
CONVERT(VARBINARY(8000),CONVERT(VARCHAR(250), [Name Change Name 2] COLLATE Latin1_General_CI_AI))
FROM [Cronus$Customer]

-- now you use the converted data from the temp table #NonUnicode to update the Customer table in NAV
UPDATE [Cronus$Customer]
SET Name = #NonUnicode.Name,
[Search Name] = #NonUnicode.[Search Name],
[Name 2] = #NonUnicode.[Name 2],
Address = #NonUnicode.Address,
[Address 2] = #NonUnicode.[Address 2],
City = #NonUnicode.City,
Contact = #NonUnicode.Contact,
[Address 3] = #NonUnicode.[Address 3],
[Name Change Name 2] = #NonUnicode.[Name Change Name 2]
FROM [Cronus$Customer]
INNER JOIN #NonUnicode ON [Cronus$Customer].No_ COLLATE Latin1_General_CI_AI = #NonUnicode.No_


That's all folks.