Double byte data truncation in Navision (SQL) 4.0 client

lisakins123

We have a Navision 4.0 client. We are modifying input fields for our AP system to be exported to a secondary system.

We are dealing with a chines double-byte character set. A couple of these fields will contain English/Chinese combinations.

In order to have these export from SQL properly we need to change the field to an nvarchar(XX) instead of the standard varchar (XX).

When we leave the field varchar(56) we can type in English and have no issue, or in Chinese characters and have no issue (28 characters as is expected with a double byte character set).

However when we modify the sql type to nvarchar(56) the field truncates in Navision. The strange thing is that we can type in 28 Chinese or 56 English characters and they are all displayed and stored in SQL properly. But in Navision both the table and the form, they show exactly half of the characters actually stored. So if we type in 18 Chinese characters, then the table and form show the first 9. If we type in 24 then Navision table and form show 12.

Has anyone on the forum ever encountered this and is there a solution that can be used to force Navision to show the true characters as they exist in the SQL table/field?

vremeni4

Hi,

I think the problem is that NAV4 does not support Unicode.
The first NAV version to support Unicode is NAV 2013.
As NAV4 does not support Unicode, all text fields on SQL server have the datatype varchar.

Now, If you change the datatype on the SQL server from varchar to nvarchar, NAV4 still does not know what to do with it, hence unexpected results.

By the way changing the datatype on SQL server is a very bad practise, as it can have all sorts of unexpected results e.g. as application NAV does not support it.

If you are only after data, you can do a technical upgrade from NAV4 to NAV2009 and then to NAV 2013.
It is a very quick process and it will transfer all fields from varchar to nvarchar.

I hope this helps.
Thanks.

lisakins123

I believe you are correct. I've tried every move I can think of to force this.

If we didn't have to export the data to another SQL server the varchar would be fine. It is this requirement that has me hunting for a work around.

Unfortunately we are a huge, global company with very little NAV and complete modifications throughout the system. An upgrade would be a huge undertaking.

Thank you for answering though.

vremeni4

Hi,

If we didn't have to export the data to another SQL server the varchar would be fine.

I do not quite understand why do you need nvarchar for data export.
If you use e.g. SSIS (SQL Server Integration Service) when you do the mapping you can link the source fields in varchar to destination fields in nvarchar.
Or you can use Bcp tool to export and import the data, or simply NAV dataport.

Unfortunately we are a huge, global company with very little NAV and complete modifications throughout the system. An upgrade would be a huge undertaking.

There is a big difference between full upgrade and technical upgrade.
I ran a technical upgrade from NAV37 to NAV 2009, on a database of 350GB (120 Users) in size and it was done in one hour.
The NAV client can be deployed via Group policy, so the users would get the new NAV installed automatically.
With NAV 2009 you would get more flexibility for example you would be able to use Web services and the client in a way handles better Unicode data.
On the other hand I agree the upgrade to NAV 2013 would be a problem(time consuming and a big task) as you would need to convert forms and reports to pages and new rdlc reports.

I hope this helps.
Thanks.