Navision Sql Database issue

i have navision sql database with customization done in attain. now i cant take backup thru navision, but its fine it i take sql backup and restore.

what can be the reason and any idea to solve it.

further, i cant restore thru veritas 11 d but able to do the same thru sql server.

i suspect their is something to do with conversion of certain navision data types/ characteristics to sql server

Hi Amer,

What error do you get when restoring the fbk?

/TH

Hi!

If any kind of codepage error occurs during restore of a FBK, then you could try to disable the “Validate Codepage” flag (File - Database - Alter - Codepage).

Does this help?

Error message:

There is an eroor in the database structure. The error (1190 in module 19) may have been caused by the computer or a program.

Table : G/L Entry

Company:

Key fields: Entry No.

19-1190 #Err_DB_AlphaTypeIncorrect DB_Err(1190)

Basically it looks like someone has broken the first and most fundamental rule of Navision on SQL. i.e. DON’T touch base tables directly through SQL.

The solution at first hand looks very simple, since it can be done in a few minutes through SQL, BUT it needs to be done by someone that really knows what they are doing, since done wrongly can seriously damage your system, and most likely make it impossible to post GL Journals.

What has to be done is that the Record that was damaged needs to be deleted in SQL, and then rebuilt internally in Navision.

Hmmm … mostly this 1190 happens, if lowercase characters or “special” characters were inserted into CODE fields, using external interfaces (as David mentioned: inserted from other programs or via interfaces like DTS or SSIS). CODE is actually “varchar” on SQL Server, which is similar to TEXT in Navision. So, such “varchar” fields could store these characters without problems, but when C/SIDE is validating (during backup or DB Test) them, this error could be raised …

The tricky part is to determine these “wrong” field contents, then you have to re-enter the value through NAV - here C/SIDE is converting any lowercase stuff into uppercase.

This conversion also could be done via SQL which is much faster (as David already said), but first you should know which fields are effected … if you know which “column” needs to be re-worked, then you don’t need to re-insert the whole records …

Yes, I am taking a worse case scenario, but I am always nervous to give users the easy solution, in case it causes more problems.

In most cases I have been able to fix this issue with a non printing report that scans the lines (Without reading the data) the just does something like

Validate(MyCodeField,MyCodefield);

Modify;

This converts the data, BUT I have only found this to work on Secondary key issues, not when the Primary key is the error. In this case (going by the error message) it looks liek the corupt data is in the primary key.

Yes, with Primary Key fields you have to do some annoying DELETE / re-INSERT stuff …

Doing this with SQL could look like this:

update [CRONUS International Ltd_$MyTable] set [MyCodeField] = upper([MyCodeField])

This changes all fields from this column at once, no matter if it’s PK field or not - but of course without the NAV validation. Hence, there are several ways to get rid of error 1190!

Can I just change that a little and say that “yes” this is correct, but only for a SQL expert such as your self. For someone that does not have your level of skill, they really should do it though C/SIDE.

Of course the ideal solution is to hire an expert <insert advert for Jörg here> and get the job done properly. [:D]

the good thing is G/L table’s Entry No field is integer both in Navision and Sql. and the query to find anything other than numeric value stored in this field give no negative info.

Hmmm … unfortunately the error message is somewhat “misleading”, as far as I know it always refers to the Primary Key fields of an “infected” table … I recently had the same error case on the “Item” table, where the error was also referring to “No.” (Code/varchar) but the “false” characters were saved in “Serial No.” … so, it is not too easy to find the exact error reason …

Moved to the SQL Forums