Native to SQL Migration => Language Problems

deV.ch

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?

David_Singleton

I think best is to not check collation, then solve the issue once you have it in SQL.

Marije_Brummel

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.

deV.ch

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!

David_Singleton

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.

deV.ch

What do you mean by fixing? The entered data must be in czech langauge because the company in czech is using this
And they need to do it later on...

David_Singleton

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.

deV.ch

Hmmm the problem is that the values are correct, they are entered in czech language. but i can't change colation to Latin2 because we also have swiss-german entries (ä,ö,ü,é,è)

so i can't "correct" the entries because they are correct...

David_Singleton

Then Mark is probably right.

deV.ch

Hmm then i guessi need to setup 2 databases? Seperate the czech Company in a own database? That would be an option right?

kine

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...

deV.ch

As from what i know, the czech values are only in the Data per company tables.

But main point is, that when making and restoring the backup, you need to set correct language for non-unicode apps, and collation.

Can you explain this further? Do i need to change Regional Settings? keyboard-layout too?

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?

kine

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.

David_Singleton

I still don't see why you can't use Latin 2 which contains both Czech and German Characters.

Marije_Brummel

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.

kine

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.

David_Singleton

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.

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.

David_Singleton

Mark Brummel wrote:

However we ran into sorting issues which is also part of the collation.

Yes definitely sorting will remain an issue.

deV.ch

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.

kine

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...

deV.ch

hmmm which means i would need to create the company-tables my self (script) set the correct collation and then import the nav-backup? Does the backup system handle that? Or does it simply drop the existing & create new tables?

kine

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...

deV.ch

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.