Restore error on sql server

ta5

Hi

While restoring a fbk (backed up from a Navision 3.7) on sql server 2005 I get the ffw error:

"The Gen. Journal Line table contains a value in a Code field that cannot be used with Microsoft Business Solutions-Navision: Field External Document No., Value I-104.427.250ã."

Please note: This behavior is reproducable and the last digit of the original value entered in the field was "§" (paragraph sign). In the error message it is shown as "ã" (a with tilde).


The restore operation is stopped then. However, if database properties are changed (database,alter) collation: validate code page=no, the restore is successful. The last sign of the restored value is shown then as "ã" (a with tilde).

This is quite strange, especially backup and restore were made on exactly the same machine. ](*,)

Any ideas?
Thomas

kine

1) Which language you are using on the native server? (the Regional settings in control panels, last tab, language for nonunicode apps.)
2) Which collation you are using on the MS SQL for your DB?

ta5

@Kine:
Thanks for your answer.
In regional and language settings there are different code page conversion tables. I'll check a few other things and will hopefully post the solution here.
Regards
Thomas

ta5

Ok, here we go:

What I did:

* Backup on a german system * Restore on sql on a english system. This caused the error mentioned above. * So I made the restore on the english system, but native. This worked, but the characters were converted (§ to ã), but I didnt notice that. * Backup the restored database again to see whether this can be restored now * Restore on sql on the english system did not work, although the backup was made on the same system. Same error as before, the ã was not valid

The solution:
Backup and restore should be performed on the same system. The problem was, I had no connection to the original db from the english system, this means I had only the backup with me..

It seems to work now

kine

:-) Yes, the systems with different codepages are big pain...

ara3n

while we are on same subject of different code pages. We have a client that has a chinese db and US. The objects are the same US version, they are on different codepages. Chinese CPR and English US. As well as different collation.

The reason they are on different db is because navision did not allow double byte characters.
Navisino recently released docs that you can simply change fin.stx and you can use double byte characters.

I downloaded the fin.stx files and restored the db. I can see the data all scrambled, after changing to codepage to Chinese was I able to see the chinese characters.

So in order to combine the db, the citrix box has to change the codepage to chinese, but what collation should I use? I tried binary, but still get error if the chinese characters are part of a key.

mgm

Another Collation issue.

We have several customer abroad, all using SQL2000. In our central database we have copies of theirs.
One of them in Turkey. They have Turkish collation.
They made a backup (.fbk). We won't to restore it, but get the same error as Thomas.
If I try to Alter the Collation of the database on the server (I can choose Turkish out of the drop down box), it won't change it.
Could this be because the client does not have the code page installed?

Maarten

kine

Problem is, that if you make the backup on PC which has another codepage for non-unicode applications than the Collation on SQL, you will have problems when restoring, because many characters will be cross-translated. In generally, the option "check codepage" is not good to have disabled.

mgm

The backup was made on the server over there. So no problems with the "check codepage" there. I am right if I think the problem is the client here?

kine

Main tip:

1) Backup and restore must be done on PC with same codepage.
2) Backup and restore must use DB with same SQL collation

additional point:
3) PC codepage and SQL collation codepage is same

if these two things are OK, you will not have this problem.
If something is set in another way, you can have wrong result (restore was finished, but you see incorrect characters in NAV etc.)

Point 3 is there to precede problems with cross-translated characters...

mgm

Thank you Kamil.
It's very clear.

kriki

[Topic moved from Navision forum to SQL General forum]