Native to SQL Migration => Language Problems
deV.ch
Member Posts: 543
I'am trying to migrate a native db to sql. This database has entries in different languages. DES & CSY. When i try to import the fbk file i get an error that
a Field in a Table has a value that can't be used with Microsoft Dynamics NAV Classic.
The Value is a CSY value.
The SQL-Collation is Latin1_General_100_AS_CI
I guess it's a collation problem, but which collation should i use and what are the downwards of using another collation? Do i have other options?
a Field in a Table has a value that can't be used with Microsoft Dynamics NAV Classic.
The Value is a CSY value.
The SQL-Collation is Latin1_General_100_AS_CI
I guess it's a collation problem, but which collation should i use and what are the downwards of using another collation? Do i have other options?
0
Comments
-
I think best is to not check collation, then solve the issue once you have it in SQL.David Singleton0
-
What is DES / CSY??
Latin1 is for most western european countries.
Use the collation that is meant to use in the customers country, otherwise you'll run into issues since NAV only allows collations that match the .stx file.
If DES/CSY requires two different collations you're basically screwed until NAV7.0 -
Is this the option under File->Database->Change => Tab Collation - Validate Collation?
@Mark
DES = German (Switherland)
CSY = Czech
Both languages are used in the database (Different Nav-Company) They work in different countries...
I don't know if different collations are needed, that's why i'am asking
i hope i'am not screwed until nav7.... thats no option!0 -
Latin 1 wont work for Czech, so how are you entering Czech characters? Either way fixing this in native DB is going to be messy, so I would just get the data into SQL and fix it there.
I am sure Kamil has done this 100 times and will be able to give some better advice.
David Singleton0 -
deV.ch wrote:What do you mean by fixing? The entered data must be in czech langauge because the company in czech is using this
The problem could be from many sources, but the most likely one is that a customer name has been entered in the Native database with an invalid character. When you enter the name code converts this to upper case. Now when you convert to SQL the CP is different, so the uppercase command gives different results.
So what you do is figure out the correct collation for SQL then turn off validation of code page then import the backup. Once imported go and find where the conversions are wrong and fix it, generally a simple loop through all records validating the Description or Name field will solve a lot of these issues.
I see this a lot with LS Retail where the server is on SQL and the POSs are on Native.David Singleton0 -
Then Mark is probably right.
David Singleton0 -
Czech is Latin 2, but this codepage includes some german characters too (see http://en.wikipedia.org/wiki/ISO/IEC_8859-2)
But main point is, that when making and restoring the backup, you need to set correct language for non-unicode apps, and collation. Collation could be set per database, or per table (manually, by script). Of course, problems could be tables which are not per company... but...0 -
As from what i know, the czech values are only in the Data per company tables.
Can you explain this further? Do i need to change Regional Settings? keyboard-layout too?But main point is, that when making and restoring the backup, you need to set correct language for non-unicode apps, and collation.
Another question: Why is it no problem to restore the he nav-backup in a native db? I haven't changed language (regional settings) and that works, but i read a lot about that scenario too and people say it doesn't work?0 -
It is complicated. If you have Native DB with some characters, during backup they are stored based on the langauge for non-unicode apps. Than, when restoring into SQL, again this language is used to transform the characters to the SQL collation used somehow. If there is some mixture, it could be problem. I am not sure, what is the main settings which choose in which ccodepage are the data in Native DB stored, if the language for nonunicode apps of the client or on the NAV DB Server. This could be another point... I means, if there is some problem, than you need to make sure that all this is in line.0
-
I still don't see why you can't use Latin 2 which contains both Czech and German Characters.David Singleton0
-
I did some consulting for a project where we combined Danish and Dutch in one database. They use different collations.
After testing we decided that Dutch should be possible with Danish collation since Dutch has not many "weird" characters.
However we ran into sorting issues which is also part of the collation.
I would setup a test environment with all options and start doing some testing. If necessairy, hire a specialist to help you.0 -
I think that problem could be DB with two companies, where one is accessed from computers with Czech language, and the second one from computers with German language. Than the backup mix both into one, and restore make it even worse, because double translation or something like that... May be the way is to make backup of each company separately from computer with same language, and restore it from same computer into SQL.0
-
I would say 99% sure that's the problem. But I still think its easier just to import the backup and then go though and fix it once its in SQL.kine wrote:I think that problem could be DB with two companies, where one is accessed from computers with Czech language, and the second one from computers with German language.David Singleton0 -
Mark Brummel wrote:However we ran into sorting issues which is also part of the collation.
Yes definitely sorting will remain an issue.David Singleton0 -
Latin2 can't be used with the german( swiss) db because there are customers with french characters (éè) one of them is not in Latin2...
However this could be a solution, or as said, setting latin2 for only the czech company tables.
For importing the nav-backup i thought about splitting the companys too. i think i will go with that, first import all but czech and then try with the czech one and play with regional settings, to see if this helps.
If i disable collation check, import czech company, change collation of that tables to Latin2, and everthing works as expected, sorting included. are there any aditional downwards of this solution that i missed?
btw: Customer access the nav through citrix.0 -
The order of things could be critical if you want to preserve the correct characters. E.g. like changing collation before/after restore could have impact...0
-
No necessarily - the tables are created with collation of the database... what about creating the DB with correct collation, restore first company, set the collation on tables, change database collation, restore second company... or something similar...0
-
sounds like a plan :-k 8)
So then i'll either do it with this method and check if i can handle newly created tables (trigger? changing collation by t-sql after creation?)
Or if this is too buggy i'll go for a database split aproach, having 2 Databases should be aceptable, i hope.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions