Restore Problem

Mally

Dear Concern
I m using NAV 4.0 SP3 SQL DB size approx. 22GB. I took backup from NAV SQL DB, when I restore this backup in Native Database, it is giving me an error:
Overflow in Decimal

What can be cause and solution?

Belias

are there some external tools that populates your sql?
If i remember well, i've seen this error when (i don't know why) i had 15,000000000000001 in a sql decimal field (sql precision is 15 decimals, nav is less)

Mally

Dear concern
How can I find this value in Navision Database(SQL Database).Means in which table and in which filed this value is?

Belias

the most important part of my post is "is there some external tool in your database that populates navision tables? (e.g.: store procedures etc."
if there's such a tool, you can easily know which tables are populated from it (simply ask to the owner/developer of the tool)...and in these table you'll probably find values like the one i post.
One more time: I am not 100% sure that values like the one i posted before is the cause.
P.S.: woohooo! 1K posts!! \:D/ \:D/ \:D/ \:D/ \:D/

Mally

Dear concern
No External tool is used to populate the Navision tables. I m sure in this.Please give soltuion how to resolve this problem?

Belias

maybe you had "SQL datatype" property set on some of your fields in your SQL db?
if not, i think there's some data with too much precision, or a decimal value in your sql db has a value too large for a nav db...in the second case, i think there's a MAX function in sql that can return you the highest value...but it will be a pain to do this, as you don't know what table and what field has the problem...anyone else have some better idea? :-k

EDIT: Obvious question: objects of sql and navision databases are the same right?or at least you're restoring a full backup (objs + data)
if objects are not the same, you can incur in unexpected problems

JedrzejT

Hi

It is possible to insert Too big decimals to table from Navision.. for example;
I have Tbl with field = 100000000000000,00 (max count of numbers for decimal)
I can't add another one number from client , but you can from code:

Tbl.Field:=Tbl.Field*10;
Tbl.modify;

I don't know is this help you..Don't know either if this decimal issue has influence on backup. Maybe, so:
I have once problem with too big value in decimal (which can't be used by Nav with some activities - like simple edit or crash "Adjust cost - item entries" -report by error of decimal )
I put my function for find this problematic values. You could try

This simple function help you localize table, field and currentkey of too big decimals , but on 22 GB maybe need a lot of time .. so maybe test only the table which can have probably very big values (or very big precision)
But like i said .. i don't know if this help you.

Tbl.SETRANGE(Tbl."Table No.",5802); 
IF Tbl.FIND('-') THEN BEGIN
  RecRef.OPEN(Tbl."Table No.");
  IF RecRef.FIND('-') THEN BEGIN
    REPEAT
      FieldRec.SETRANGE(FieldRec.TableNo,RecRef.NUMBER);
      IF FieldRec.FINDSET THEN BEGIN
        REPEAT
          IF FORMAT(FieldRec.Type)='Decimal' THEN BEGIN  //i dont know how to filter by this
            FldRef:=RecRef.FIELD(FieldRec."No.");
            IF EVALUATE(DecVariable,FORMAT(FldRef.VALUE))=FALSE THEN //evaluate becasuse assign not produce error
              MESSAGE(Tbl."Table Name" + ' ' + FldRef.NAME + ' ' +RecRef.CURRENTKEY);
          END;
        UNTIL FieldRec.NEXT=0
      END;

    UNTIL RecRef.NEXT=0
  END;
END;

EDIT: tbl - Table information , fieldrec - Field

Belias

Hi

It is possible to insert Too big decimals to table from Navision.. for example;
I have Tbl with field = 100000000000000,00 (max count of numbers for decimal)
I can't add another one number from client , but you can from code:


Code: Select all
Tbl.Field:=Tbl.Field*10;
Tbl.modify;

yes, i forgot about that!thanks to have reminded to me, too! :thumbsup:

Mally

Dear concern
Please tell me that what r the Datatypes of "tbl" and "fieldrec" variables.

Belias

Mally wrote:

Dear concern
Please tell me that what r the Datatypes of "tbl" and "fieldrec" variables.

tbl: type = record, subtype= table information
fieldrec: type = record, subtype = field

IF FORMAT(FieldRec.Type)='Decimal' THEN BEGIN //i dont know how to filter by this

like every Option field in navision:

fieldrec.setrange(type,fieldrec.type::Decimal);

Mally

Dear Concern
This code doesnt help this code only tell me the name of the table 5802 and name of field with type decimal nothing else.My problem doesnt solve. My problem is to find the value of decimal type field which has big value which is acsessible
by SQL not by Native

Belias

can you post the message you received please?
that message should say you what is the record, and the field with the wrong decimal number (at least this is what i understood after a fast look to JedrzejT code)

Mally

Dear concern
It is not showing any wrong decimal type.It is showing me followin statement:
Value entry Item Ledger Entry Quantity Entry No.

when I m pressing ok it si showing me another field instead of Item Ledger Entry Quantity like Invoiced Quantity and many other fields of decimal type.

Belias

You're right, as i said, i give only a fast look to code...try to add this to the message

MESSAGE(Tbl."Table Name" + ' ' + FldRef.NAME + ' ' +RecRef.CURRENTKEY + ' ' + Format(FldRef.VALUE));

it should return also the wrong value

P.S.: i don't remember the exact syntax, but recref.getposition should return the Primary key values, useful to retrieve where's the wrong number (use F1 hepl for this function)

Mally

Dear concern
The message only appears if remove false and put true instead of false from following error:

IF EVALUATE(DecVariable,FORMAT(FldRef.VALUE)) = false THEN //evaluate becasuse assign not produce error

It is showing me all values with 0 and without 0.

Please tell me that there is 2 information in fieldRefrence table named(Length and record) when I m pressing F5.what is the meaning of this Length and Record informations?

And I put false in evaluate function then it is not showing me mesages otherwise showing message.

Belias

Mally wrote:

Dear concern
The message only appears if remove false and put true instead of false from following error:
IF EVALUATE(DecVariable,FORMAT(FldRef.VALUE)) = false THEN //evaluate becasuse assign not produce error

It is showing me all values with 0 and without 0.

Please tell me that there is 2 information in fieldRefrence table named(Length and record) when I m pressing F5.what is the meaning of this Length and Record informations?

And I put false in evaluate function then it is not showing me mesages otherwise showing message.

Excuse me in advance for this somewhat rude post, but i'd like to be faster and clearer as possible.
Have you got an idea of what does this process do?i don't think so, as you changed a fundamental condition in order to get your wrong values (see underlined text i quoted). if you use true instead of false, you'll get only the good values...in your case, every value.
as you can say from the first lines of code, the process has been run on only one table (5802). if you don't get any message, clearly this table is not a problem. I don't know how expert are you but you should really ask someone more expert than you to give you a hand...we can try to do as much as possible from the forum, but not everything!
P.S.: i hope you run the process on SQL database from where the back up comes from
P.P.S.: Length and Record??? i think you are doing something wrong...can you post your codeunit please? (export the object and post it)

Mally

Dear Concern
Y we r searching records of only Value Entry (ID 5802) table y not all objects? If there is any wrong value in others tables than we cannot find the wrong value.

Belias

the function is only a sample, you should do little modifications (e.g.: remove the filter on table no., or change it to another table)
didn't that fixed number in the code ring you a bell? :shock:

Mally

Dear Concern
I have done little modifications but how I'll know that in which table has wrong value, it means that I have run this code for all tables.Am I right?

Belias

yes, you're right!(good luck)
you can also remove the filter to run the code for all the tables and run the process right before leaving work (test it, because the process is not mine and i don't know if it has been done to work for all tables).
If you run the process like this, it should be better to create a log text file (or table if you prefer) instead of firing an unknown number of messages...

Mally

Dear Concern
I have created 1 form and 1 command button on that form and write following code on OnPush trogger of that button:


win.OPEN(Text001);
LineNo := 0;
Tbl.RESET;
Tbl.SETRANGE(Tbl."Company Name",COMPANYNAME);
IF Tbl.FIND('-') THEN
REPEAT
win.UPDATE(1,Tbl."Table No.");
RecRef.OPEN(Tbl."Table No.");
IF RecRef.FIND('-') THEN
REPEAT
FieldRec.RESET;
FieldRec.SETRANGE(FieldRec.TableNo,RecRef.NUMBER);
FieldRec.SETRANGE(FieldRec.Type,FieldRec.Type::Decimal);
IF FieldRec.FINDSET THEN
REPEAT
FldRef := RecRef.FIELD(FieldRec."No.");
IF EVALUATE(DecVariable,FORMAT(FldRef.VALUE)) = FALSE THEN BEGIN
LineNo := LineNo + 10000;
Today1.INIT;
Today1."Table No." := Tbl."Table No.";
Today1."Line No." := LineNo;
Today1."Table Name" := Tbl."Table Name";
Today1."Field No." := FldRef.NUMBER;
Today1."Field Name" := FldRef.NAME;
Today1."Field Type" := 'Decimal';
Today1."Field Value" := FldRef.VALUE;
Today1.INSERT;
COMMIT;
END;
UNTIL FieldRec.NEXT=0
UNTIL RecRef.NEXT=0
UNTIL Tbl.NEXT = 0;
win.CLOSE;

where Text001 : Updating Process #1##############



When I pressing that command button, after some time it shwos me an error of "Another user has chaged,Please start ur activity again".

ara3n

run it when nobody is in the database.

Belias

I was assuming you were not on production Database... :shock:
Follow ara3n advice

Mally

Dear concern
What do u mean by production Database?

Mally

Dear concern
I run that code when nobody is on the database then also it is showing me same error(Another user hase modified.Start your activity again).

Mally

Dear Concern
Nobody is in Database again it is showing me same error,but it is giving me on the table ID( 13711 (Excise Posting Setup)) again and agian:

"Another user has changed the definition of the Today table after the activity was started.
Start again."

Belias

try to run your code for table 13711 only with the debugger turned on.
if that message appear even if nobody than you is using the database, then YOU are the "another user" of the message.
i also noticed that you don't do a
recref.close after this instruction:

UNTIL RecRef.NEXT=0

after trying the above suggestions, try these solutions if you still have the problem:

substitute

Today1.INIT;

with

clear(today1)

remove the COMMIT

Mally

Dear concern
I have try all your solution,Still I m facing d same error.

Please lookp my following code:

win.OPEN(Text001);
LineNo := 0;
Tbl.RESET;
Tbl.SETRANGE(Tbl."Company Name",COMPANYNAME);
Tbl.SETRANGE(Tbl."Table No.",13711);
IF Tbl.FIND('-') THEN
REPEAT
win.UPDATE(1,Tbl."Table No.");
RecRef.OPEN(Tbl."Table No.");
IF RecRef.FIND('-') THEN
REPEAT
FieldRec.RESET;
FieldRec.SETRANGE(FieldRec.TableNo,RecRef.NUMBER);
FieldRec.SETRANGE(FieldRec.Type,FieldRec.Type::Decimal);
IF FieldRec.FINDSET THEN
REPEAT
FldRef := RecRef.FIELD(FieldRec."No.");
IF EVALUATE(DecVariable,FORMAT(FldRef.VALUE)) = FALSE THEN BEGIN
LineNo := LineNo + 10000;
CLEAR(Today1);
//Today1.INIT;
Today1."Table No." := Tbl."Table No.";
Today1."Line No." := LineNo;
Today1."Table Name" := Tbl."Table Name";
Today1."Field No." := FldRef.NUMBER;
Today1."Field Name" := FldRef.NAME;
Today1."Field Type" := 'Decimal';
Today1."Field Value" := FldRef.VALUE;
Today1.INSERT;
//MESSAGE(Tbl."Table Name" + ', ' + FldRef.NAME + ', ' + RecRef.CURRENTKEY + ', ' + FORMAT(FldRef.VALUE));
END;
UNTIL FieldRec.NEXT=0;
UNTIL RecRef.NEXT=0;
RecRef.CLOSE;
UNTIL Tbl.NEXT = 0;
win.CLOSE;
#-o

Belias

and where does the debugger stops?

edit:!! what is the subtype of record variable "Today1"? please don't tell me it's "Field" table [-o<
the Log table must be a newly created table! if you insert records in a table you're looping through, you will easily fall into unexpected errors!

Mally

The debugger stop at:
Today1.INSERT;

Belias

as i thought...reread my edited post:
you should create a new table where you log the errors, you can't use Field table...excuse me if i give you only some guidelines, but i don't have time to guide you step by step today :roll: