SOLUTION: Corrupted SIFT tables with 4.0 SP3

FDickschat

Dear all,

as we all know there was an issue where renaming a record would corrupt the SIFT tables which was solved with SP3. But still with 4.0 SP3 it is possible to get corrupted SIFT tables and this is due to the use of scripts which delete the 0 amount SIFT records or actually by just using Navisions own optimise function which also deletes 0 amount SIFT records.

Problem
After deleting a record from [choose a table] a flow field which calculates some value from that table does show an incorrect value. A lookup on the table gives a different result then the flow field. Switching "Maintain SIFT" off and back on will solve the problem.

Explanation
In case of a deletion of a record the trigger, which creates or updates the SIFT records only handles updates but not inserts. The trigger assumes that previously created SIFT records are never deleted. But as many customers nowadays run scripts to exactly do that and Navisions own optimise function also deletes 0 amount records the assumption is incorrect.

To reproduce the problem do the following in Cronus:

1. Create a new G/L Account: No. = 1101 Name = SIFT 2. Create a codeunit with this code: OnRun() GLEntry.FINDLAST; GLEntry."Entry No." := GLEntry."Entry No." + 1; GLEntry.INIT; GLEntry."G/L Account No." := '1101'; GLEntry.Amount := 1; // Change this line in step 3 GLEntry.INSERT; Run the CU. 3. Change the codeunit to insert a record with Amount = -1, and run it again. 4. Notice that the new account now has a balance of 0. (SIFT Total = 0). 5. Run Optimize on table 17 6. In Navision, run table 17 and delete the last record (the one where Amount = -1). Now the balance on account 1101 should be back to 1. 7. Run Chart of Account - the balance is 0! Drill down in Net Change, and see that the total of the entries = 1! If you design table 17 and disable MaintainSIFTIndex for the 2nd key, then re-enable, then the SIFT is correctly now back to 1. Instead of Step 5, run this on SQL to delete the 0-amount SIFT record or to view it: Select * delete from "CRONUS International Ltd_$17$0" WHERE bucket = 1 AND "f3" = '1101' AND "f4" = '1753.01.01'

Solution
MS corrected the code that creates the SIFT triggers. Since today there is a new 4.0 SP3, Update 5 available. The update process makes several client updates necessary:
1) Update to 4.0 SP3, Update 1 (Build 24080).
2) Update to 4.0 SP3, Update 5 (Build 24734).
3) Apply a Hotfix (Build 24843)

Be aware: After applying the Hotfix and connecting with a client to the DB all SIFT tables are recreated. This might be a very time consuming process depending on your DB size. If you have that problem contact MS. Update 6 will probably make the recreation of the SIFT tables optional.

Best regards, Frank

kriki

FDickschat wrote:

MS corrected the code that creates the SIFT triggers. Since today there is a new 4.0 SP3, Update 5 available. The update process makes several client updates necessary:
1) Update to 4.0 SP3, Update 1 (Build 24080).
2) Update to 4.0 SP3, Update 5 (Build 24734).
3) Apply a Hotfix (Build 24843)

And where can we find those? I tried to search the partnersource, but I didn't find them.

pdj

kriki wrote:

And where can we find those? I tried to search the partnersource, but I didn't find them.

http://www.mibuso.com/forum/viewtopic.php?p=90906

Arhontis

After some search:
Table of updates (which is also found on http://www.waldo.be/):
https://mbs.microsoft.com/partnersource ... page=false

Released code fixes for Microsoft Dynamics NAV 4.0:
https://mbs.microsoft.com/knowledgebase ... QYXOWLUTVM

I couldn't find the Update 5 or the 24843...

colin_leversuch-roberts

currently about to test sp3 upgrade - will post back on this - we thought the SIFT table "fix" to the bug introduced in sp1 and not fixed in sp2 was in the default release of sp3.

colin_leversuch-roberts

you need update 1,5 and the hotfix which will also rebuild your SIFT tables.
Update 6 is due august and may have an option to not rebuild the SIFT.

colin_leversuch-roberts

just to confirm the SIFT table rebuilds:-
there are two problems - the date with renamed account which is fixed by a SIFT table rebuild - this was a problem for us as we figured it would take several days to rebuild the SIFT tables - we were provided with a script which could verify if we needed to rebuild SIFT's - you'll have to check with your supplier.
There's also another problem with SIFT's if you're removed 0 value records - also requires table rebuild - there should be a fix which avoids the SIFT rebuild for this issue - release due in august I believe ( UK )

FDickschat

Dear all,

After some tests we found out that the Hotfix fixes single row deletes but not multi row deletes. :evil: #-o

The SIFT trigger decides whether only a single record is deleted or several records are deleted and then updates the SIFT records. But the fix MS did was only for single record deletes. ](*,)

We have involved MS again to get a fix for the fix. MS has confirmed this to be a problem and has escalated it to the highest level. The next hotfix is scheduled for August.

Best regards, Frank

colin_leversuch-roberts

we've applied sp3 release 5 to our test system , with a fix to avoid rebuilding sift tables, and all seems to be well - also fixed a problem we had with not seeming to be able to create or ammend logins, had to apply trace flag 4146 to the sql server.
Still testing - sql 2005 sp2 , we also have a sql rollup 3179 to apply which may fix an issue which is related in sql server.

FDickschat

Dear all,

NAV 4.0 SP3 Build 24843 did not correctly replace the SIFT triggers.

MS did create a fix in the meantime to correct the SIFT creating triggers (build 4.0.3.24971 - KB 940245) but the fix was withdrawn as it created some other problems.

The final fix for the SIFT problems is now scheduled for update 6 end of August.

Best regards, Frank

Wim_Mulder

Hi folks,
just to inform you, unfortunately, the Navision 4.0 SP3 Update 6, will probably take 2 weeks longer, as I have heard last week from Microsoft!

ara3n

I guess that means update 1 for 5.0 will take longer as well.

Any reason why?

Wim_Mulder

Hallo,

sorry, it is me again.
I was just talking to Microsoft and I have great news!

The Update 6 (build 25143) is released this morning and it should fix the "corrupted SIFT tables".
Im not sure if the Update 1 Navision 5.0 is also released!

The Database will be converted, and as far as I could see in the profiler, trigger will be recreated and sift-tables are rebuild. (This will takes some time)

groeten
Wim

PS: Navision includes the INDEX-Hinting from now on and we got rid of option (fast ??)

Peter_Wijntjes

A warning for anyone with a large database who wants to install hotfix 5 or 6. Since all SIFT records are recreated the upgrade will not only take a lot of time but also cause a significant growth of the SQL Server logfile.

In order to prevent SQL Server to crash due to out of disk space on log disk check your logfile space prior to installing the hotfix.

And reserve enough time for the update to complete.

jensthomsen

What is the content of Update 6 (except for the SIFT corrections)? Does update 6 contain previous updates (1-5?)? And is it really necessary to use update 6?

Marije_Brummel

http://www.mibuso.com/forum/viewtopic.p ... highlight=

BE CAREFUL WITH UPDATE 6.
:shock:

Waldo

jensthomsen wrote:

What is the content of Update 6 (except for the SIFT corrections)? Does update 6 contain previous updates (1-5?)? And is it really necessary to use update 6?

To answer your question ... this is what I blogged a few days ago:

The KB article has been released. You can find it here[link].

Nice thing to know is that this hotfix accumulates all previous hotfixes.

Here is a list of most important problems that are corrected:

NAV crashes when emailing from contact card (office2007)
Printing a large report with image in the header: "There is not enough memory to execute this function"
When you deleteSIFT records, tot totals are calculated incorrectly
Design problems on request form
4.0SP3 was incompatible with latest version of dw20.exe .
Budget: an amount with more than 14 digits
NODBC problems when upgrade from Sp2
Database test, saving to a file or event log results in error message.
RecordID variable as parameter restulted into an error.
When using a dataport on a nonexisting option string: NAV didn't respond anymore
Clicking a value in a flowfilter: nothing happened.
Did not provide index hints for all queries (this is one that has to be investigated)
No special characters in company name

It is recommended that you should only apply this hotfix to systems that are experiencing one of these specific problems! You should contact Microsoft to get the hotfix.

My blog: http://dynamicsuser.net/blogs/waldo/archive/2007/09/16/nav-4-0-sp3-update-6-final-info.aspx

ara3n

If you are using nas, nodbc, cfront you need update 5

If you are using Native Navision, You need Update 4 for Nav server.


So update 6 is only for the client. exe.

FCP

With all this SIFT problems.
with the index hinting overriding the query optimizer.

Is really worth it the risk. :-k

By the way can we disable the Index Hinting Overrinding the query Optimizer?? :?:

Marije_Brummel

Yes you can, please read my whitepaper about this.

http://www.mibuso.com/dlinfo.asp?FileID=896

Good luck. (and be carefull. )

Waldo

FCP wrote:

With all this SIFT problems.
with the index hinting overriding the query optimizer.

Is really worth it the risk. :-k

By the way can we disable the Index Hinting Overrinding the query Optimizer?? :?:

To make it easy for you:

In Enterprise Manager (SQL2000) or SQL Server Management Console, create the a table with following statement:

CREATE TABLE [$ndo$dbconfig] (config VARCHAR(512) NOT NULL) 
GRANT SELECT ON [$ndo$dbconfig] TO public

With this next statement, you disable the indexhints:

INSERT INTO [$ndo$dbconfig] 
VALUES ('IndexHint=No')

Now, the old situation is restored. If you want, can add indexhinting one by one by statements like:

INSERT INTO [$ndo$dbconfig] 
VALUES ('IndexHint=Yes;Company="DEFAULT COMPANY";Table="Vendor";Key="Search Name";Search Method="-+$";Index=1')

Source: my blog

Marije_Brummel

My advise is to leave companyname and search method empty unless you are sure what you are doing. (You should be in the first place off-course).

I have heard so many people talking about index hinting in the wrong context that I have become very carefull to even talk about it.

That's why I really ask you to read the whitepaper for the full story!

Waldo

You just want everyone to read your white paper
I want everyone to read my blog

Marije_Brummel

So just to help you advertising

http://www.waldo.be/

O:)