mibuso.com
Home› SQL General
Options

Rebuild SIFT Tables

megawavezmegawavez Member Posts: 133
edited 2008-08-27 in SQL General
Hi,

Couldn't find anything using a search - is there anyway to force the system to rebuild all the SQL SIFT Tables? I can remove, then add the SIFT indexes, but this is time consuming.

We are looking at clearing out our old company and starting again. I want to delete all history (ledger entries, posted pos, etc.) in SQL (truncate). I need to system to rebuild all the SIFT tables after the information has been cleared.

Thanks,

Mega

Comments

  • Options
    kinekine Member Posts: 12,562
    If you truncate some table, you need to truncate the SIFT tables for this table (have the ID of the table as number in the name of the table). Because if there are no records in the table, there are no records in the SIFT table too... ;-)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    megawavezmegawavez Member Posts: 133
    kine wrote:
    If you truncate some table, you need to truncate the SIFT tables for this table (have the ID of the table as number in the name of the table). Because if there are no records in the table, there are no records in the SIFT table too... ;-)

    That partially helps - is there anyway to force the system to rebuilt the contents of the table?

    Thanks.
  • Options
    kinekine Member Posts: 12,562
    Disable and enable SIFT or Nav native Backup/Restore procedure.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • Options
    WaldoWaldo Member Posts: 3,412
    Well ... there is ... but it involves some coding.

    You can play around with the virtual table "Key".
    If you just test it out by:
    1) create a new tabular form with a wizard on the "Key" table
    2) Preview that form.
    3) uncheck the checkbox (MaintainSIFTIndex) on the G/L Entry Table on one of the keys that contains a SumIndexFields
    4) save by leaving the record
    You'll see you won't have the sift tables anymore.

    So, you could create some kind of functionality (report, codeunit, ...) to save the current value, switch off the MaintainSIFTIndex, save the record (COMMIT?), and afterwards put it back on. This doesn't take long at all.. .

    Watch out for this though:
    - the date and time of the object is changed as well. you can solve this by updating the object table while updating the key table
    - You can't save the SIFT levels through the key table. May be there's another virtual tables I don't know of ... . So I think (didn't test this, sorry) all levels will be activated afterwards.

    Hope this helps. May be I'll work out something later on my blog... . Looks interesting.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Options
    WaldoWaldo Member Posts: 3,412
    :oops:
    I just noticed that you're posting in the "Financials" forum, so i suppose the "Key" table is nog available yet... . Sorry.

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
  • Options
    megawavezmegawavez Member Posts: 133
    Waldo wrote:
    :oops:
    I just noticed that you're posting in the "Financials" forum, so i suppose the "Key" table is nog available yet... . Sorry.

    We're using the 3.7 executables, but only the 2.6 database.
  • Options
    garakgarak Member Posts: 3,263
    you must disable all SIFTS and store the the table.
    After this you must enable your needed sifts (are there buckets in these version :?: ) and now your FlowField work
    Do you make it right, it works too!
  • Options
    krikikriki Member, Moderator Posts: 9,096
    [Topic moved from Navision Financials forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    WaldoWaldo Member Posts: 3,412
    As addition ... see my blogpostfor more information on the "Key"-table... .
    8)

    Eric Wauters
    MVP - Microsoft Dynamics NAV
    My blog
Sign In or Register to comment.