Navision SQL Tables 17$0, 17$1, 17$2, etc....

I delete all records from Table 17 GL Entry, and I still have all the records in Tables 17$0, 17$1, 17$2 etc… Navision 4.0 SQL It is not deleting from these tables. Any Comment ???

How did you delete the records - through Navision?

wassimkh why are you deleting the G/L enries? As Steffen implies, are you going ot SQL, and just trying to delete the records there. If so, thn the fact that it is not deleting the SIFT fields, is trivial compared to the rest of the porblems you are going to have. Deleting core Navsion tables is not that simple.

I wonder what truly motivates this question?..

Guys, I deleted the records from the GL Entry table from SQL & from Navision(I’ve Tried Both Ways), and still I have the records in tables 17$1,17$2,etc… THX Anyway

Just out of curiosity, can you tell us why you need to delete the data in Table 17 G/L Entry?

After you do this from Navision, try optimizing the keys from Navision, that should do the trick. I don’t know why it would leave those records in there, but it should be cleared when you optimize. File/Database/Information, hit the Tables button, browse to table 17, highlight it (select the line) and hit the optimize button.

Records are only ever deleted from the SIFT tables (the $0, $1 …) by Navision when using the Optimize function Daniel mentions. Navision runs a query on all SIFT tables, for the base table being optimized, to delete records with zero values for all sum fields(if you look at the Profiler you will see the query) - they are unneeded because doing a SELECT against those records gives the same result if either the sums are zero, or the records are not there. All other SIFT records will remain after the Optimize. Deleting records from the base table like G/L Entry (either from Navision or from a SQL tool) causes the trigger on that table not to delete from the SIFT tables but to update the sum values in them; thus there is never a DELETE operation done on the SIFT tables. If you want that, you must manually from SQL, delete all records from each SIFT table associated with a base table (use TRUNCATE and the deleted records will not be written to the transaction log, or DELETE FROM and they will be). You must know what you’re doing. This is a pure data-driven answer - I would consider answering David/Nelson’s question before doing anything.

In terms of the actual issue of managing the SIFT fields, HM Business Solutions have a tool that can fix and maintain these. But again, please be very carefull when deleting code Navision tables.