Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 385 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Double byte data truncation in Navision (SQL) 4.0 client
lisakins123
Member Posts: 3
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?
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?
0
Comments
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.
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.
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.
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.