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
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)
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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/
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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;
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:
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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
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)
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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.
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)
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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.
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)
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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.
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:
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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?
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...
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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).
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."
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
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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!
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
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:
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso My Blog
Comments
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)
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
How can I find this value in Navision Database(SQL Database).Means in which table and in which filed this value is?
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/
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
No External tool is used to populate the Navision tables. I m sure in this.Please give soltuion how to resolve this problem?
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
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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:
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.
EDIT: tbl - Table information , fieldrec - Field
yes, i forgot about that!thanks to have reminded to me, too! :thumbsup:
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
Please tell me that what r the Datatypes of "tbl" and "fieldrec" variables.
fieldrec: type = record, subtype = field
like every Option field in navision:
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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
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)
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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.
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)
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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.
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)
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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.
didn't that fixed number in the code ring you a bell? :shock:
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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?
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...
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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".
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
Follow ara3n advice
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
What do u mean by production Database?
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).
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."
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:
after trying the above suggestions, try these solutions if you still have the problem:
substitute with
remove the COMMIT
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
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
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!
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
Today1.INSERT;
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:
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog