Error restoring native db into sql 2005

BGI
Member Posts: 176
Hi,
when we restored a db into sql 2005.. we get the following error:
- The CREATE UNIQUE INDEX statement terminated becaus a duplicate key was found for the object name '...$VSIFT$1' ande the index name 'VSIFTIDX. The duplicate....
I looked into the table, and there are 3 keys with the same sumindexfield, but on different keys. When is set MaintainSIFTindex to NO, the keys are created.
Now my question: how can this happen?
Secondly, when i disable maintainSIFTindex, do the simindexfields still get created and are the usable without this?
Rgds
when we restored a db into sql 2005.. we get the following error:
- The CREATE UNIQUE INDEX statement terminated becaus a duplicate key was found for the object name '...$VSIFT$1' ande the index name 'VSIFTIDX. The duplicate....
I looked into the table, and there are 3 keys with the same sumindexfield, but on different keys. When is set MaintainSIFTindex to NO, the keys are created.
Now my question: how can this happen?
Secondly, when i disable maintainSIFTindex, do the simindexfields still get created and are the usable without this?
Rgds
Rgds
Benny Giebens
Benny Giebens
0
Answers
-
[Topic moved from Navision forum to SQL General forum]
Try to restore the SQL-DB with in index properties MaintainSIFTIndex=No.
If there isn't a SIFTIndex to use to calculate a total, NAV goes to the real table to do the calculation, so this answers your question if it is still usable.
Once in the SQL-DB, try to put the index properties MaintainSIFTIndex to yes one by one and see if you still have the same error.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
I already changed the setting of maintainsiftndex to no, and it restores correctly.
I only want to figure out why this is happening, the sumindexfield is also present on the primary key and 2 secondary keys, but secondary keys can have duplicates, or am i missing the point completly...Rgds
Benny Giebens0 -
Can you post the keys (all of them) which have flowfields + also the flowfields?
This way, we can check it out.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
These are the keys
as you can see it's the second and the 3th one that gives the problem...
The needed data is a to mutch to post here...
- Deelnemer,Boekjaar ;Clustered=Yes
- BH,Cons,Inschrijving verstuurd,Inschrijving ontvangen,Aard,JR,Boekhoudbureau code,Betaling opgevraagd,Begintoelage aangevraagd,Datum controle uitslag,Uitslag verstuurd,Eindtoelage aangevraagd,Datum afgewerkt,Voorlichtingstoelage aangevraa,Datum beginlijst toelage,Datum toelage aangevraag,Beginjaar toelage,DL;
SumIndexFields=Dummy,Tarief;
BackupKey=Yes;
MaintainSIFTIndex=No
-Inschrijving ontvangen,Begintoelage aangevraagd,Eindtoelage aangevraagd,Voorlichtingstoelage aangevraa,Begintoelage binnen,Eindtoelage binnen,Voorlichtingstoelage binnen,Datum beginlijst toelage,Datum toelage aangevraag,Datum toelage binnen,Milieu:Akkoord voor aanvraag,Milieu:subsidie aangevraagd,Milieu:subsidie ontvangen;
SumIndexFields=Dummy;
BackupKey=Yes
- BH,Cons,Aard,Inschrijving ontvangen,Gegevens binnen,Datum eindcontrole,Datum controle uitslag,Datum geponst/afgetapt,Boekhoudbureau code,Datum vragenlijst buiten,Datum vragenlijst opgesteld,Milieu:Akkoord voor aanvraag,Milieu:subsidie aangevraagd,Milieu:subsidie ontvangen,JR,Milieu: Verslag-voorlopig,Milieu: Verslag-definitief,DL;
SumIndexFields=Dummy;
BackupKey=Yes;
MaintainSIFTIndex=No }
- BH,Cons,Aard,Boekhoudbureau code,Datum vragenlijst opgesteld,OP,Datum vragenlijst buiten,Datum vragenlijst binnen,Datum eindcontrole,Datum afgewerkt,Uitslag verstuurd,JR,Inschrijving ontvangen,Beginjaar toelage,Verslag:Voorlopig binnen op,Datum toelage aangevraag,Voorlichtingstoelage aangevraa,DL;
SumIndexFields=Dummy }
- Boekjaar,Startmaand,Uitslag verstuurd,Inschrijving ontvangen,Milieu:Akkoord voor aanvraag,Milieu:subsidie aangevraagd,Milieu:subsidie ontvangen,JR }
;Gegevens binnenRgds
Benny Giebens0 -
I found it:
SQL can only use up to 16 fields in the index. In this 16 fields are included the fields of the primary key that are not yet used in the secondary key.
Are you sure you need all these monstrous indexes?
Updating them is a process that takes a lot of time.
I would do some index-tuning on these. If you create some new keys (REALLY needed) to find the records/ SIFT-totals, you would get some performance improvement too.
For the moment you can put the MaintainSIFTIndex property to No. Your application will work anyway (but maybe slower).
BTW : before going life, you would better do some overall SQLtuning on the DB.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
BGI wrote:These are the keys
as you can see it's the second and the 3th one that gives the problem...
The needed data is a to mutch to post here...
- Deelnemer,Boekjaar ;Clustered=Yes
- BH,Cons,Inschrijving verstuurd,Inschrijving ontvangen,Aard,JR,Boekhoudbureau code,Betaling opgevraagd,Begintoelage aangevraagd,Datum controle uitslag,Uitslag verstuurd,Eindtoelage aangevraagd,Datum afgewerkt,Voorlichtingstoelage aangevraa,Datum beginlijst toelage,Datum toelage aangevraag,Beginjaar toelage,DL;
SumIndexFields=Dummy,Tarief;
BackupKey=Yes;
MaintainSIFTIndex=No
-Inschrijving ontvangen,Begintoelage aangevraagd,Eindtoelage aangevraagd,Voorlichtingstoelage aangevraa,Begintoelage binnen,Eindtoelage binnen,Voorlichtingstoelage binnen,Datum beginlijst toelage,Datum toelage aangevraag,Datum toelage binnen,Milieu:Akkoord voor aanvraag,Milieu:subsidie aangevraagd,Milieu:subsidie ontvangen;
SumIndexFields=Dummy;
BackupKey=Yes
- BH,Cons,Aard,Inschrijving ontvangen,Gegevens binnen,Datum eindcontrole,Datum controle uitslag,Datum geponst/afgetapt,Boekhoudbureau code,Datum vragenlijst buiten,Datum vragenlijst opgesteld,Milieu:Akkoord voor aanvraag,Milieu:subsidie aangevraagd,Milieu:subsidie ontvangen,JR,Milieu: Verslag-voorlopig,Milieu: Verslag-definitief,DL;
SumIndexFields=Dummy;
BackupKey=Yes;
MaintainSIFTIndex=No }
- BH,Cons,Aard,Boekhoudbureau code,Datum vragenlijst opgesteld,OP,Datum vragenlijst buiten,Datum vragenlijst binnen,Datum eindcontrole,Datum afgewerkt,Uitslag verstuurd,JR,Inschrijving ontvangen,Beginjaar toelage,Verslag:Voorlopig binnen op,Datum toelage aangevraag,Voorlichtingstoelage aangevraa,DL;
SumIndexFields=Dummy }
- Boekjaar,Startmaand,Uitslag verstuurd,Inschrijving ontvangen,Milieu:Akkoord voor aanvraag,Milieu:subsidie aangevraagd,Milieu:subsidie ontvangen,JR }
;Gegevens binnen
One remark - you are really developing in another language than in English? I recommend to develop in ENU layer, naming the fields in English, and use local language only in the captions... :?0 -
Off course....stupid of me not to think about that....
First step is to get working on the db in sql and do testing...
Second step tuning...
This is a database of a customer of ours, who have done the devloping their self...is large customer and has his own developer licence...
In fact they don't use anything of navision standard, and have build a complete custom application...
So we can only give advice in how to use variables etc....and they decide how tp do it..
For all our development, everything is in ENU, except captions off course....Rgds
Benny Giebens0 -
-
[Can you change the attribute of the topic to <Solved> (or also <Good Posting>) and remove the text from the title?]
Please see Forum Rule(s) #3.9: Forum Rules.Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!0 -
Sorry, my mistake....is taken care of...Rgds
Benny Giebens0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions