Does anyone have any idea why the following SQL would fail:

ALTER TABLE "Inspections"
DROP CONSTRAINT "PersonID"

when I run the SQL, I am getting a nasty AV error message

"Elevate Error #9999 access violation at xxxxxxxxxxx in edbserver.exe"

then if I run it again I get the error:

"Elevate Error #401 the source constraint 4510 does not exist in the constraint FK_Farmers "

... these 2 errors alternate!

The constraint links to a "Farmers" data table in the same database, and this constraint (FK_Farmers) does exist, and can be repaired / optimized.

There are no other dependencies.

I have tried to REPAIR and OPTIMIZE both tables, particularly on the Index which is accessed by this particular constraint, to no effect.

I am really confused by this as I would expect a DROP CONSTRAINT statement to execute pretty easily.

Adam,

<< Does anyone have any idea why the following SQL would fail:

ALTER TABLE "Inspections"
DROP CONSTRAINT "PersonID"

when I run the SQL, I am getting a nasty AV error message  >>

The AV is just a side-effect tear-down problem caused by the constraint
issue, but I've fixed it.

What version are you using ?

It sounds like these bugs that were fixed a while ago, but may have infected
your catalog:

http://www.elevatesoft.com/incident?action=viewrep&category=edb&release=2.12&incident=3822

The end result of these bugs would be a source table reference in a FK with
the wrong table ID.

If these catalogs have been in play since 2.12/2.13, then that's definitely
the cause.

Tim Young
Elevate Software
www.elevatesoft.com

Tim

Thanks for the response

>>What version are you using ?

2.19.2

>>If these catalogs have been in play since 2.12/2.13, then that's definitely
>>the cause.

The tables are 6 or 8 years old, so I am sure they predate this issue. Probably I haven't set things up to OPTIMIZE often enough ...

I am unsure what actions I can take to fix the table ... exporting it to CSV and re-importing it is an obvious option, but a lighter touch option would be preferred.

Further on this:

I am realising the problem may exist in a number of the tables in this database, as I am getting similar errors on some of the other tables as I try to restructure them.

I realise that the problem shouldn't exist for newer tables created post 2.12, but most of my tables (in multiple users' systems) are a lot older than this.

It would be incredibly useful to have a command which would clean the table to remove this problem.

Neither REPAIR or OPTIMIZE seem to do it.

The issue is that due to dependencies with things like VIEWs exporting and re-importing tables is hard work, as all dependent VIEWs must be dropped and recreated, which is hard when dozens of VIEWs are involved.

Adam

>The issue is that due to dependencies with things like VIEWs exporting and re-importing tables is hard work, as all dependent VIEWs must be dropped and recreated, which is hard when dozens of VIEWs are involved.

Whilst its still a pain in the bum its possible to write some Delphi code to use the catalog and grab everything you need, export the tables, dump the database, recreate it and reload the data. Since I try not to use database enforced RI (got bitten by it once to often) and haven't used many views I can't do much with my data but if you want to ship me a database (non-unicode) I'll happily produce a program to run through and do it. It may be useful for others as well.

Roy Lambert

Roy

Thanks, this is a super generous offer!

Right now I am trying to do something similar with SQL ... using similar mechanisms. It is hard to automate for all possible databases as dependencies mean that objects need to be created in specific orders ...

I don't think I can ask you to do it Roy!! If I make any useful progress I will let you know.

Adam

>Thanks, this is a super generous offer!
>
>Right now I am trying to do something similar with SQL ... using similar mechanisms. It is hard to automate for all possible databases as dependencies mean that objects need to be created in specific orders ...
>
>I don't think I can ask you to do it Roy!! If I make any useful progress I will let you know.


Fair enough - if you do need help let me know - just remember this is a hobby for me and has to fit round trying to make money so will take longer

Roy

Adam,

<< I am realising the problem may exist in a number of the tables in this
database, as I am getting similar errors on some of the other tables as I
try to restructure them. >>

The problem isn't with the tables, it's in the catalog.  Specifically,
there's a duplicate reverse-constraint-reference for the Staff table's
primary key that is messing up the constraint drop for the Inspections
table.

I'm working on an addition to ElevateDB that will allow you to repair a
catalog, and that will fix this issue.  It will take a bit of doing,
however, so please give me a few days.

Tim Young
Elevate Software
www.elevatesoft.com

Per my last email:

"I'm working on an addition to ElevateDB that will allow you to repair a
catalog, and that will fix this issue.  It will take a bit of doing,
however, so please give me a few days."

What I ended up doing was adding a fix to the catalog loading that will
transparently correct this issue whenever a catalog is loaded.  So, it will
simply stop what is occurring, and the next time the catalog is updated, the
fixes will get permanently fixed on disk.

So, you'll be able to just deploye 2.10 B3 and you'll be all set.

Tim Young
Elevate Software
www.elevatesoft.com

>>So, you'll be able to just deploy 2.10 B3 and you'll be all set.

Great work, thanks Tim.