Postcode Problem restoring SQL database

Datapac

I'm trying to restore a Navision 4.0 SQL database for some testing. I’m getting a problem everytime I try to restore. As soon as it tries to restore records into the Post Code Table, it says records already exist for Code “4460”, City “4460 GRACE HOLLOGNE”. I’ve checked the database and there is no violation of the Primary key constraint (Code,City) on the file for 4460, even though there are 5 records with this Code. The other field in the Post Code table, Search City, is blank for all five records

Code	City	                			Search City
4460	4460 Grace Hollogne	
4460	4460 GRACE HOLLOGNE	
4460	IMPORT	
4460	Rue Louis Bleriot 5	
4460	tesr

I checked the OnValidate function of the fields and discovered the following:

Code - OnValidate()
PostCode.SETCURRENTKEY("Search City");
PostCode.SETRANGE("Search City","Search City");
PostCode.SETRANGE(Code,Code);
IF PostCode.FIND('-') THEN
  ERROR(Text000,FIELDCAPTION(Code),Code);

This code seems to conflict with the Primary Key & suggests that the Primary Key should be Code, Search City? I don’t know how the 5 records above were added to the Post Code table.
I can restore the database to a Proprietary database. Could it be that the SQL restore tries to Validate the Code and fails, while the proprietary restore doesn’t run the validate?

I’ve checked SP1 code and the primary key and OnValidate trigger are still the same.

This looks like a bug to me, I think I'll have to comment out the OnValidate, or apply a datafix to allow this to be restored. Any ideas?

Thanks,
Richie

HalMdy

Verify if in the record you don't have something like "â" or "ä", that could give a problem in SQL...

Albertvh

Hi Richie,

Looks like you have an old version of the Post Code table the "Search Name" or that it wasn't migrated correctly should have the City name in it, perhaps you could write a codeunit to validate the City which in turn will update the "Search City" field.

Feldballe

My guess would be that the SQL-server are set up with property CS (Case sensitive). That way the two first records would appear to be exactly the same when you restore the backup in a native db.

Datapac

Thanks guys,
I've discovered the root of the problem, someone made the Search City visible and editable on the Post Code lookup form. Users were entering Code & City and the Search City was defaulting to UPPERCASE(City). The Users were then able to clear the Search City field, so that the validate of the Code field would fail if it was called, but it was not being called.

Code.Validate must be called in the SQL restore routine. I've made the Search City invisible and ineditable on the lookup, now I just have to fix the data!

BGI

The only problem you have is that the collation sequence on the sql server is wrong. For navision databases you need to have a collation sequence that is case sensitive. If not sql sees twice the same key (key is code + city), and stops.
Even in the cronus belgiuym dataabse there are entries with the same code and city, but one is uppercase the other not...
So there is nothing wrong in code, only sql server is setup wrong..

Datapac

Thanks, collation sequence was wrong, it was Case Insensitive! Something to be aware of in future when creating my databases.