Can anyone offer some assistance …? We have a user that is using v3.60SQL and seems to encounter problems when running thier consildation…they receive the following message ; A G/L Entry with posting date on a closing date(C29/12/02) was found while exporting non-closing entries. G/L Account no 11010. I am only able to replicate the error in a SQL version of Navision which makes me think that this must be something to do with SQL and not Navision …does anyone know of any difference between the two that would cause this ? Many thanks, L
Hallo Lisa, the most important difference you should be aware of is the sorting order of Code fields containing numerical data. In the native database for example the sort order is 1, 2, 3, 10, 20, 30. In the SQL version this would be sorted 1, 10, 2, 20, 3, 30 (which, from the pure database point of view, is correct!). So if you are using numerical G/L account numbers, be sure to left-pad them to identical length using zeroes. Staying with my example: 01, 02, 03, 10, 20, 30. That should probably do the trick for you. Kind regards, Thomas
Hi! Thomas, I don’t think the sorting is the problem here. Well, the difference between native and SQL in case of the “Posting Date” is, that the native C29.12.02 is (it should!) converted to a 29.12.02 23:59:59 in SQL! Lisa, is there any chance that somehow this time-info is wrong (in the SQL table)? Could you check via “Enterprise Manager”?
Hi Jörg, I must admit I was not aware of the different closing-date-behaviour. I had suspected that (with the wrong sorting) some wrong G/L entries would be taken into the consolidation. Thanks for pointing me (and maybe Lisa) at something new again, Thomas
Hi, The sorting order can be changed via the property of the “No.” field on the GL Account table with a property called “SQL Data type” to be set as ‘variable’. As regards the closing entry, zoom on the this entry and find if it really has been marked as ‘closing’ and that the date may be wrongly prefixed with a ‘C’ Do let us know the outcome when you find the reason ! Cheers,
quote:
The sorting order can be changed via the property of the “No.” field on the GL Account table with a property called “SQL Data type” to be set as ‘variable’
Yeah, but that a “tricky one”: if you change the SQL Datatype if the field No. (Code20) then you also have to change the SQL Datatype of all fields that are related to No., e.g. in G/L Entry, Gen. Journal Line, Purchase Line, Sales Line … and so on and so on …
Hi Joerg, It is not necessary to change the SQL Datatype for other related tables. Atleast I have not experienced any errors or messages which stop the entries because of the change in this property. Can you be more specific as to the necessity for changing the property for other related tables ? Thanks
A few comments. The problem is not with the formation of the date value in SQL. If the date is not a properly formed non-closing date format and also not a closing format (with the 23:59:59) then Navision will error with a c/side error: The table contains a Date field that uses invalid data for the time component of the DATETIME value: What you’re seeing is an application generated error. I suspect the reason you see this in SQL and not native is due to a c/side driver error giving incorrect results that causes the application to give a logical error. I hope that any such error (there have been a few) have been corrected by the latest 3.60 c/side hotfix but it is possible that only 3.70 has received the latest of these. I would have to check into that. What is your c/side version (finsql.exe version stamp)? By the way, the app code should not make assumptions about the order of code fields (or the order of any data for that matter, with the exception of cronological processing. Sorting is not a static concept; it depends on code pages, collations etc). To do so is introducing potential functional errors. I can’t say it ‘does’ not though. Its ok to change a Code SQL subtype to Variant, but only if it is done accross all tables.
But it can be problem of Navision on MS SQL, when sometime Navision does not “see” all entries (it is problem with transfer Item LE, adjustments etc…) - in some situations you know, that there are 2 records in the filter but after first NEXT C/AL say that there is no more records… Fix is execute this command: UPDATE [$ndo$dbproperty] SET diagnostics = 131072
Rohith:
quote:
Can you be more specific as to the necessity for changing the property for other related tables ?
Here we go … The field “No.” (Code20) is the primary key of the “G/L Account” table. The database property “Maintain Relationships” is set to FALSE by default, so the (SQL)tables, that are related to this field, e.g. “G/L Entry”, do not have a “Foreign Key” relation to this field. In this case you are right, no error will occur (will it?). But if you once want to use this “Maintain Relationships” feature, then the “Foreign Key” relation could not be established due to the different SQL datatypes. So, for compatibility reasons you should change the “SQL datatype” in all related tables as well …
quote:
UPDATE [$ndo$dbproperty] SET diagnostics = 131072
Originally posted by Kine - 2005 May 11 : 10:28:39
And exactly what does this update do?
It (flag 131072) disables an optimization to avoid using SQL cursors in some situations when doing FIND(’-’), to instead use a default result set - or firehose as its sometimes called. This is more efficient that SQL cursors. There was a problem with this but it should be addressed by obtaining the latest c/side hotifx and not by changing this value, although as a short-term solution is is useful.