How to recreate data in the SIFT table Company$17$0

Kreit

Hi all.
I dropped through SQL SIFT table Company$17$0, because data inside this table was not correct.
How to recreate data, table very big (200GB), I try enable and disable flag MaintainSIFTIndex, but nothing happened.
Help me,please.
P.S. Navision 4.0 SP3 and SQL Server 2008.

kapamarou

Kreit wrote:

because data inside this table was not correct.

How could it be wrong?

Do you get an error message when you try to enable disable the indexes maintenance?

Kreit

kapamarou wrote:

Kreit wrote:

because data inside this table was not correct.

How could it be wrong?

Do you get an error message when you try to enable disable the indexes maintenance?

1)I don't know.
2)No, without errors. When i disable this property, SIFT is not delete.When i enable - table is not create.
Now i try to recreate a primary key, but SIFT 17$0 is empty.

kapamarou

Did you try to recreate the table Company$17$0? If you did, then try to edit the table through NAV and delete the whole key, save your table then recreate it at the same position with the exact same sumindexes and properties.

I tried this with a dummy database and it seems to work (haven't thoroughly tested it though, just checked if table 15 G/L account is ok). I strongly recommend trying this in a test environment. According to your table size, it could take a while.

Kreit

kapamarou wrote:

Did you try to recreate the table Company$17$0? If you did, then try to edit the table through NAV and delete the whole key, save your table then recreate it at the same position with the exact same sumindexes and properties.

I tried this with a dummy database and it seems to work (haven't thoroughly tested it though, just checked if table 15 G/L account is ok). I strongly recommend trying this in a test environment. According to your table size, it could take a while.

Sorry,but i don't understand, what i must do?

kapamarou

I tried this with a dummy database. When I dropped the sift table 17$0, if I tried to change the key through NAV I got an error saying that t17$0 does not exist. So I through SQL Managmenr Studio I scripted table 17$1 and edited the script and changed 17$1 to 17$0 along with the constraint. This resulted in the existence of table 17$0 in the database (although the structure was completely wrong when compared to the original one). This enabled me to design the NAV table through the Object designer, go to the keys, hit F4 to delete the whole key, save the table without getting any error, and then design the table again, and recreate the deleted key at the exact position it was and with exactly the same properties.

kine

Set the "MaintainSIFTOnSQL" to NO. Save the object. Close the designer. Reopen the designer, set it to YES, save the object. It should recreate the table...

Kreit

kine wrote:

Set the "MaintainSIFTOnSQL" to NO. Save the object. Close the designer. Reopen the designer, set it to YES, save the object. It should recreate the table...

Yeah, it should, but doesn't work.

kine

And are you working on correct database? ;-) It MUST work, or problem is somewhere else... like setting the property on wrong key, not saving the object between changes etc.

Slawek_Guzek

kine wrote:

And are you working on correct database? ;-) It MUST work, or problem is somewhere else... like setting the property on wrong key, not saving the object between changes etc.

It does not necessarily work I'm afraid.

Unfortunately NAV remembers internally SIFT state, and if you delete SIFT table from SQL then if you un-check maintain SIFT NAV will still try to drop the table.. Which of course end up with SQL error

My advice would be first to create dummy table named Company$17$0, then un-check maintain SIFT or remove SIFT fields on PK and save table in NAV (Company$17$0 table should get dropped), then re-check or re-define SIFT fields and save table.

Regards,
Slawek

stryk

Slawek Guzek wrote:

kine wrote:

And are you working on correct database? ;-) It MUST work, or problem is somewhere else... like setting the property on wrong key, not saving the object between changes etc.

It does not necessarily work I'm afraid.

Unfortunately NAV remembers internally SIFT state, and if you delete SIFT table from SQL then if you un-check maintain SIFT NAV will still try to drop the table.. Which of course end up with SQL error

My advice would be first to create dummy table named Company$17$0, then un-check maintain SIFT or remove SIFT fields on PK and save table in NAV (Company$17$0 table should get dropped), then re-check or re-define SIFT fields and save table.

Regards,
Slawek

As Kamil said: it MUST work, if the problem is just an accidentally deleted SIFT table.

What you propose is actually the same kapamarou and kine already suggested ...