Accent problems with Restore - character not valid

andy76andy76 Member Posts: 616
edited 2008-10-28 in SQL General
Hello,

we have this problem: a backup of a database NAV50SP1 on Sql Server2005 made with backup of client NAV (on the server side) cannot be restored on another server Sql Server 2005 on a new database.
It seems it can not convert the accented words in record of type code.
I attach an example of the message that comes during restoring process for each word having accents.

The right word is CONTROLLO QUALITÀ and I see it correct on the table of the server of the customer.

The regional settings and the collation are the same between the two server.

Do you have some suggestions?

Thank you

Andy

Comments

  • David_SingletonDavid_Singleton Member Posts: 5,479
    best solution is to go to the Chart of Accounts and change the name of the account.

    The problem is that you have a code page miss match between the two databases (and before you reply ... Yes You Do :mrgreen: ).

    The problem is that in Navision the lower case accented a is being converted to a different upper case than the once recognized in the second database. You can play with collations till you work it out, or just change it to something that works.

    If you absolutely MUST have the accented lower case a, then go to the SEARCH DESCRIPTION field and in that field use a plain unaccented A.
    David Singleton
  • andy76andy76 Member Posts: 616
    The problem is that there are a lot of accented words in the database and not only the example in the g/l account and that the user could insert a lot of new accented words in the future.

    I hope to have a solution not temporary but effective.

    Thank you very much
  • David_SingletonDavid_Singleton Member Posts: 5,479
    andy76 wrote:
    The problem is that there are a lot of accented words in the database and not only the example in the g/l account and that the user could insert a lot of new accented words in the future.

    I hope to have a solution not temporary but effective.

    Thank you very much

    Then you need to work out the correct code page to use.
    David Singleton
  • andy76andy76 Member Posts: 616
    How can we understand the mismatch?
    Where are these codepages listed?

    Thank you again
  • kinekine Member Posts: 12,562
    Each DB on SQLhave something called Collation. Check that both datbases have same collation. You can see it from NAV client in File-Database-Alter... dialog.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • andy76andy76 Member Posts: 616
    Also if I create a new empty database with this collation Latin1_General_CI_AI identical to the one of customer side the error on restoring appears.

    The only way to restore it is disabling the check 'Validate Codepage' so the words with accents are imported with strange characters.

    Can you explain?Is the database however valid?

    I don't think that the problem is of collation but of codepage but I don't know what it means.

    Thank you
  • MalajloMalajlo Member Posts: 294
    I had some problems with small letters in fields of type Code. External app wrote some data directly on SQL.

    I used (you can change code to do CONVERTSTR)
    RecRef.OPEN(CorrTable);
    IF RecRef.FIND('-') THEN 
      REPEAT 
        FOR i := 1 TO RecRef.FIELDCOUNT DO BEGIN 
          FldRef := RecRef.FIELDINDEX(i); 
          IF (UPPERCASE(FORMAT(FldRef.TYPE)) = 'CODE') AND (UPPERCASE(FORMAT(FldRef.CLASS)) = 'NORMAL') THEN BEGIN
              FldRef.VALUE := UPPERCASE(FORMAT(FldRef.VALUE)) ;
              RecRef.MODIFY; 
          END; 
        END; 
      UNTIL RecRef.NEXT = 0;
    
  • krikikriki Member, Moderator Posts: 9,115
    [Topic moved from 'NAV/Navision' forum to 'SQL General' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • andy76andy76 Member Posts: 616
    Sorry but I only want to know how to have the same Codepage between the two servers without writing code.
    The problem could happen in every table and I cannot write functions or code to manage all them..

    I hope you can understand.

    I also want to know if the backup restored on the same server (customer) is valid 100% or could have problems after a disaster recovery for example.

    Thank you.
  • kinekine Member Posts: 12,562
    1) Source and target DB must have same Collation
    2) PC used for making the backup and restoring the backup must have same language for non-unicode application set in the Local settings...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
Sign In or Register to comment.