Corrupt SIFT Tables

matttrax

Using Navision 3.7, SQL 2000.

It appears some of our SIFT tables are corrupt. Specifically for Item Ledger Entries. The Quantity on Hand from the SIFT tables does not match the sum of the Quantity field in Item Ledger Entry.

Dropping and Creating the indexes has not fixed the issue. Any thoughts on why this happens and how to fix it?

David_Singleton

Is it definitely a corrupt SIFT and not that someone has done something naughty iin SQL direct to the Table?

BTW what do you mean by "Dropping and Creating" that sounds like SQL terminology. So is that a typo, and are you actually deleting the key and re-creating it (or deactivate/reactive) in Navision table designer, or are you actually doing this in SQL?

matttrax

I've already tried to turn off / on the MaintainSIFTIndex property for the keys in question, as well as disabling / enabling the keys, but those give me a SQL error stating that "The qualified @oldname references a database (our database name) other than the current database." So our DBA was trying to do it from SQL Server.

Although there have been times that I have been forced to manually change things in the Item Ledger Entry table (despite my protests), this is a recent development. For the item I'm using as my test case, it shows Quantity on Hand = 1, where the sum of the Quantity fields from the drill down is actually 0. I have also confirmed with SQL scripts that the quantities are different by querying the Item Ledger Entries and SIFT table(s) directly.

I watched our DBA Drop and Create the indexes from SQL Server, and they completed successfully, but the results are the same as if nothing had been done. Thanks for your help.

David_Singleton

Your best bet now is to take a NAV fbk backup and restore it. I know it takes time, but it should fix everything. (Well the database issues at least).

matttrax

Just so I understand, I should take a native backup (fbk) of a SQL database. That backup will not have the SIFT tables because in native they are not physical objects. I can then restore that fbk (even though I'm using a sql database) and it will still work on SQL server?

David_Singleton

This is all done from Navision. But read my post on your other issue before you do this. If the issues are related you need to fix them both.

The important thing about a Navision FBK is that it only backs up the data, not the keys and sift data, so on a restore all that information is rebuilt.

davmac1

Navision has its own naming convention for constraints. There are certain objects that will cause problems if a SQL task ends up renaming (or dropping and creating) thse objects and giving them a name differenet from before.
This can be manually fixed through SQL Server by giving the objects the hard coded name Navision is using.
So your other alternative is to rename the objects correctly by looking at equivalent objects that do not have this problem.
The SIFT tables are updated thru triggers from the parent table, so you can check the trigger for the item ledger table and then the table names and field names used for the associated sift tables.

matttrax

davmac1 wrote:

There are certain objects that will cause problems if a SQL task ends up renaming (or dropping and creating) thse objects and giving them a name differenet from before.

#-o I had forgotten about this, but our DBA wrote a SQL job to "reindex" (I think drop and recreate) the indexes and possibly constraints on the most heavily hit tables in our database. I remember thinking it seemed like a bad idea at the time, but really had no evidence to contradict what was being said. I will investigate further and let everyone know what I find out.

matttrax

We have our icons setup to just open the database directly using the parameters like servername, database, and company. The icons had been setup as database=Abc when in reality the database is ABC. You could log in no problem regardless, but when you went to do certain things in SQL it would pass exactly what you typed in for the database name to the functions it was running. In this case we were disabling a key and it was renaming an index according to the SQL Trace. It failed because the capitalization on the database was incorrect.

VERY frustrating. Again, this was SQL 2000 and Navision 3.7. Not sure it was correct in later versions of either.

Slawek_Guzek

It failed because the capitalization on the database was incorrect.

It's not an error, it is database collation setting, which apparently is set to Case Sensitive.

Regards,
Slawek