Hi,
We’ve got a problem with a table where COUNT returns one less record than there are in the table.
COUNT returns 9089 records
Copy and pasting the table to Excel gives 9090 records.
SELECT * in Query Analyser also returns 9090 records.
We’ve not noticed this with other tables! The unusual thing about this table, however, is the data is imported into it using a T-Sql script, but whether this is part of the problem I don’t know.
Any ideas?
Many thanks
Justin
Copying and pasting to Excel will give you 9090 rows (9089 records plus 1 header row for the field names).
I’m not sure about the SELECT * statement result.
Yes, there are 9090 genuine rows + 1 header row in Excel . [;)]
Many thanks
Justin
I will make basica assumptions that this is on SQL, and that you are using COUNT, and not COUNTAPPROX.
We would need to know exactly how you are counting the records and importing them. If you are block importing the whole tbale, and then doing a count, then it seems odd, and definitely there is some information missing.
If you are importing records one at a time over a period, and then randomly doing a count, then there could be issues with GETLATEST VERSION or a commit issue. So we need to see a sample of the code that is causeing th e problem.
Also what version of SQL. This was a definite and known issue in version 2.60, but I am assuming you to be on a much newer version.
Hi,
Sorry - here’s a bit more info.
We are using Navision Attain V4 SP1 SQL client, although Navision 2.6 database objects, the server is SQL 2000 Enterprise Edition.
The records are counted using a flowfield, the definition of this flowfield is just
COUNT(Tablename)
I believe the records are imported indivually, but I’m checking that with the guy whowrote that bit.
Thanks for your help
Justin
Hm, I thought this had been explained by the oversight of not noticing the header on the 1st row.
Is there really something else in here?.. [:^)]
So what are the properties of the flowfield, I would assume that the coding error is in that. I am also going to assume that you have tried to log out and back in again to ensire that its not a latest version issue.
David? What coding error?.. Sorry, but I must be missing something here… [:$]
This was my first guess too, but Justin answered that one…
My guess is that the flow field is doing something different to what they expect. Or they are not counting the latest version record.
Hi,
There’s no code to have errors in! The flowfield just does a count on the table, no filters or conditions are set.
There are now 9113 records in the table according to Excel, yet COUNT now reports 9111.
I have also tried adding
MyVar := Tablename.COUNT;
to a list form and this also returns 9111. SELECT * on the SQL server returns 9113 rows.
I have also run SP_UpdateStats on the SQL server.
Many thanks
Justin
Oh, OK, I misinterpreted Justin’s reply.
Have you identified which row is missing in the count, if you copy and paste the table from within navision to excel, how many rows are there?
Is this a table you created? What is the primary key to the table, is there a row missing a value in the primary key field?
No, any suggestions on how I do that? No. of rows in Excel are as above.
This is our own table, the primary key is CorrelationID, LogDateTime where CorrelationID is type CODE 36, and LogDateTime is type DATETIME. There are no rows with a missing primary key field.
Many thanks
Justin
Can you drilldown on the flowfield and copy the resulting lines to excel, then compare with your other excel ?
Go into File/Database/Information from within Navision, click on the Tables button, find your table (will be more than one if you have multiple companies and it is not a non company specific table). See how many records are displayed as the count there. Click on the Test button for that table ONLY. See if there are any errors.
Interesting! We’ve now had some more records in the table (9162 rows now) and COUNT is now correct. Go figure.
Next time we do a build I will monitor the records in the table and do as you suggest above. I’ve tested the table and there were no errors.
Many thanks for the suggestions
Justin
Hi Justin,
did you try to count the records with a FIND - NEXT -Loop inNavision.
From do you make your copy (Query Analyser or Navision -Run Table).
br
Josef Metz
you guys are on V4 these days?
Geez time has moved on. LOL!
The issue may have been a get latest version issue. If tghe data is being added through SQL, its possible that it has not registed fully in Navision. If you see the error again, then try logging out, closing the server, restarting the SQL server, and then try again. I know this is tough to do in a production environment, but if you can do it, at least you will know if that’s the issue or not.
PS does anyone know why this thread was locked?
I locked it based on that reponse, I thought it was finished. Just trying to make sure I do my job now we have a lot less posted! [:)]