Add Index failed sql error

[got_to_love_jde]

Hi All,
I tried to add a new index to the shipping history detail file and ran into the following problem. Anyone seen this one before ? Any ideas on where to go ? There are only 4 or 5 indexes shown in One World and i can't get to the enterprise manager right now to check what indicies exist there.
Any help would be greatly appriciated.
Regards,
Dave

1232/1060 Thu Jun 20 09:59:25 2002 JDBODBC2056
ODB0000183 - SQLExecute failed

1232/1060 Thu Jun 20 09:59:25 2002 JDBODBC2056
[Microsoft][ODBC SQL Server Driver][SQL Server]There is already an index on table 'F42119' named 'F42119_10'. - SQLSTATE: S0011

1232/1060 Thu Jun 20 09:59:25 2002 jdb_drvm887
JDB9900401 - Failed to execute db request

1232/1060 Thu Jun 20 09:59:25 2002 jdb_exem604
JDB2100018 - Failed to create index 10 for table F42119 in data source Business Data - TEST

 

[Michelle D.]

Dave,

I just checked my SQL database and F42119 has 6 indexes (PK, 2, 3, 4, 8, 9).
I also double-checked the F42119.h file and it has the same 6 identified. I
have had this problem before, unfortunately, I don't remember exactly what I
did to resolve it! I remember looking everywhere under the sun for logs and
then a lot of trial and error. I think it turned out to be something really
simple ... I'm sorry I don't remember the resolution. If I think of
anything, I'll let you know.

Good luck,
Michelle



Michelle Dulay

 

[ronnet]

U can use
select * from sysindexes where name like '%f42119%'
to find out whic indexes exist
then u can use drop index
be careful with the drop.

 

[got_to_love_jde]

Ronnet,
Thanks for the tip to find out what indexes exist. I will use it when i get back to the office.

On the other hand, I will not be dropping indexes.....Too risky. Does anyone know where One World is getting it's indexing "number" ? I know SQL handles more than 10 indexes. Why is my One World insisting on using the "_10" index ?

Thanks again,
Dave

 

[Zoltan_Gyimesi]

Hi Dave (again ;-)

Just a shot into the dark:
Rebuild indicies on the table and try again.

Good luck,

Zoltán

 

[got_to_love_jde]

Hi again Zoltan,

I am back and very happy to see you still here ! You are the Champion of the Forum.

Unfortunately, I am unsure what you mean by regenerating the indexes. I was trying to generate the indexes when the problem ocurred. Do you mean I should do this in SQL itself ?

Take care friend,
Dave

ps: Some day you will have to show me how to put that accent over the "a" in your name so I can address you properly.

 

[Zoltan_Gyimesi]

Dave,

I felt suspicious that OW generate this _10 suffix and inspected the header file (.h) of this table but I didn't found any F42119_10.

Regards,

Zoltán
P.S.: First try the rebuild indicies before you try the drop - who knows, maybe it works

 

[Zoltan_Gyimesi]

Hi Dave,

Yes, I was also happy when I notified/saw that you were back (if my memory serves, I wrote you personally).

Posting a bunch onto the Forum(s) does not mean to be a champion.

About (re)generating indicies follow the steps below:
1.) Sign-on OW on a flat client ))
2.) Open the Object Management Workbench
3.) Add the table to a project of yours (possibly with project status 21)
4.) GET or CHECK-OUT the spec of the table
5.) Select Design button on the middle bar
6.) Select Table Operations tab
7.) Press the Generate Indexes icon
n.) I let you figure out the remaining ;-)

About the accent on the letter "a" in my name:
What about to Copy/Paste from my post It is a very sophisticated method. Have you ever known this method? ))

Regards,

Zoltán
P.S.: Forum, excuse me the personal phrases.

 

[got_to_love_jde]

Zoltán,
Copy and paste ?? You ARE a funny guy ! Actually i was looking for a more technical response but that works too.

I did go through those steps to regen the file indexes but that is where i ran into the problem.

Regards,
Dave

 

[got_to_love_jde]

Re: RE: Add Index failed sql error

Michelle,
I think I need your really simple answer. Sleep on it. I am sure it will come to you.
Thanks,
Dave

 

[Zoltan_Gyimesi]

Dave,

I thought, regenerate indicicies without your new index, add your index and regenerate again.
I mentioned: it is just a shot in the dark.

Your platform is Metaframe XP?
Is it an AS400 like or what?

Regards,

Zoltán
P.S.: Hey guy! Is Copy/Paste not enough TECHNICAL for you? What a DEVELOPER? ;-)

 

[got_to_love_jde]

Ooops,
I will try regenerating the indexes without my new index and then try to generate my NEW index. I did not understand that suggestion properly.
My platform is currently NT SQL. I hope to be working on the same platform for a while. ;-)
Dave

 

[got_to_love_jde]

An Update on this one......JDE solved it.

Turns out that some applications add indexes on the run and then delete them when they are done using them. One job did not delete it's temporary index. When you generate indexes, the first thing OMW will do is delete all KNOWN indexes and then rebuild them and any new ones. Since one index could not get deleted in the first step, the building of indexes stopped.

The answer was to go into SQL enterprise manager and manually remove all indexes and then do the rebuild indexes in OWM.

Thanks all,
Dave