1 Reply by AndyFoy

  • AndyFoy
  • Consultant
  • Offline
  • Registered: 09-08-2006
  • Posts: 219

Re: Taxon Dictionary Update Error

Hi,

After upgrading our test system from 6.9.3 to 6.14.4 we are trying to apply the dictionary updates.  We are currently on version 00000003 and hence have been running scripts 00000004 onwards.  We have got so far and then got an error running script 00000009.sql as follows:

----------------
Error occurred runnign script 00000009.sql.  The error is described as:
The DELETE statement conflicted with the REFERENCE constraint "FK_Index_Taxon_Designation_Taxon_List_Item".  The conflict occurred in the database "NBNData", table "dbo.Index_Taxon_Designation", column 'Taxon_List_Item_Key'
----------------

The script seems to be trying to delete old Taxon_List_Items but some of them are still referenced via entries in Index_Taxon_Designation.  Some of them have been added by us, i.e links from taxons to local taxon designation types (e.g. county rare/scarce designations) but the remainder are links to system supplied taxon designation types (e.g. to 'FEP Guidance 007: Table 2' and to 'Conservation Regulations (1994) Sch2').

Does the Index_Taxon_Designation table need to be rebuilt before this script is re-run as I am not sure the foreign references exist in the underlying Taxon_Designation table, only in the Index.  Or can the Index entries be deleted manually by us without any concern before we re-run the script?

Many thanks,
Andy

Andy Foy
Systems Manager
Greenspace Information for Greater London (GiGL) CIC
www.gigl.org.uk

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Taxon Dictionary Update Error

Hi Andy,
It sounds like an oversight in the script, but your solution to rebuild the Index_Taxon_Designation table should do the trick. That is probably a safer approach than trying to do anything manually.

Regards

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by AndyFoy

  • AndyFoy
  • Consultant
  • Offline
  • Registered: 09-08-2006
  • Posts: 219

Re: Taxon Dictionary Update Error

Hi John,

Thanks for the suggestion.  However, we still have several hundred foreign keys in the Index_Taxon_Designation table that the script is trying to delete from the Taxon_List_Items table.  Most are referring to the 'FEP Guidance 007: Table 2' designation, but there are several other designations too.  Any ideas?

Can you specify what tables and fields the Index is built from so that I can check it has rebuilt correctly?  At first sight it comes only from the Taxon_Designation table but there must be something else involved as there are more than twice as many index entries as there are unique records for the two keys on the Taxon_Designation table.

Cheers,
Andy

Andy Foy
Systems Manager
Greenspace Information for Greater London (GiGL) CIC
www.gigl.org.uk

4 Reply by AndyFoy

  • AndyFoy
  • Consultant
  • Offline
  • Registered: 09-08-2006
  • Posts: 219

Re: Taxon Dictionary Update Error

Hi,

Our IT department have just tried running the scripts again, just in case they work.  However, they now get the information message:

-------------------
Recorder upgrade complete.  No database updates were required.
-------------------

The Recorder application is still showing the script level as 00000008 and the system table is showing the same.  They have tried running the dictionaryupdate with the old scripts (i.e. 00000004 - 00000008) still present, and with them removed from the directory so that the next script to run is 00000009.  Both give the same message.

Any ideas?

Thanks,
Andy

Andy Foy
Systems Manager
Greenspace Information for Greater London (GiGL) CIC
www.gigl.org.uk

5 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Taxon Dictionary Update Error

Try deleting the contents of Index_Taxon_Designation (via SQl Server or Access link)  before running the scripts. You can rebuid thsi after the update is complete and the other indexes have been rebuilt.

Mike Weideli

MikeWeideli's Website

6 Reply by AndyFoy

  • AndyFoy
  • Consultant
  • Offline
  • Registered: 09-08-2006
  • Posts: 219

Re: Taxon Dictionary Update Error

Hi,

Thanks Mike ... that makes perfect sense.  I'll get them to try that.

Regarding our other problem ...

Recorder upgrade complete.  No database updates were required.

Our Recorder application is showing the script level as 00000008 and the system table is showing the same.  We have tried running the dictionaryupdate with the old scripts (i.e. 00000004 - 00000008) still present, and with them removed from the directory so that the next script to run is 00000009.  Both give the same message.

Does anyone have any suggestions how to get around this?

Cheers,
Andy

Andy Foy
Systems Manager
Greenspace Information for Greater London (GiGL) CIC
www.gigl.org.uk

7 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Taxon Dictionary Update Error

I'm probably stating the obvious, but they are trying to run the dictionary upgrade kit, not the application upgrade kit aren't they?

If the setting in your Dict Seq item in the Setting table says 00000008 and you are running the Dictionary Upgrade kit with a scripts folder underneath the exe that contains a file 00000009.sql then it should definitely attempt to run that file unless something very odd has gone wrong.

Best Wishes

John van Breda
Biodiverse IT

johnvanbreda's Website