1 Reply by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: Errors during Import

I have just imported a set of records into a new install of Recorder via the "NBN Access Database (Zipped)" with the following errors (only one of each variety posted):

Failed to insert SR000011000006LV into LOCATION
Database Error Message:
The UPDATE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_LOCATION_LOCATION". The conflict occurred in database "NBNData", table "dbo.LOCATION", column 'LOCATION_KEY'.The statement has been terminated.
--------------------
Failed to insert SR00001100000103 into INDIVIDUAL
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Individual_Organisation_Department". The conflict occurred in database "NBNData", table "dbo.Organisation_Department", column 'Organisation_Department_Key'.(-2147217873) The statement has been terminated.(-2147217873)
--------------------
Failed to insert SR00001100002PHE into TAXON_DETERMINATION
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TAXON_DETERMINATION_TAXON_LIST_ITEM". The conflict occurred in database "NBNData", table "dbo.TAXON_LIST_ITEM", column 'TAXON_LIST_ITEM_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
--------------------
Failed to insert SR00001100000G33 into BIOTOPE_DETERMINATION
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_BIOTOPE_DETERMINATION_BIOTOPE_LIST_ITEM". The conflict occurred in database "NBNData", table "dbo.BIOTOPE_LIST_ITEM", column 'BIOTOPE_LIST_ITEM_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
--------------------
Failed to insert SR0000110000001C into LOCATION_RELATION
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_LOCATION_RELATION_LOCATION1". The conflict occurred in database "NBNData", table "dbo.LOCATION", column 'LOCATION_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
--------------------
Failed to insert SR0000110000001D into LOCATION_RELATION
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_LOCATION_RELATION_LOCATION2". The conflict occurred in database "NBNData", table "dbo.LOCATION", column 'LOCATION_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
--------------------
Failed to insert SR000011000024EI into TAXON_OCCURRENCE_SOURCES
Database Error Message:
The occurrence record and all related data were not imported as the determination record is missing or invalid.
--------------------
Failed to insert SR00001100002UJA into TAXON_OCCURRENCE_DATA
Database Error Message:
The occurrence record and all related data were not imported as the determination record is missing or invalid.
--------------------
Failed to insert SR00001100000G33 into BIOTOPE_OCCURRENCE
Database Error Message:
The occurrence record and all related data were not imported as the determination record is missing or invalid.
--------------------

I was surprised to get so many rejected records (83) in a transfer from one copy of Recorder to another. I think I understand a couple of the errors, but could someone explain the errors encountered, so I can analyse the source, please?

Craig Slawson
Staffordshire Ecological Record

ser's Website

2 Reply by ser (edited by ser 22-10-2010 07:46:45)

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: Errors during Import

Two further imports of different datasets resulted in 154 invalid items and 91 invalid items respectively - OK this is only c0.2% of the items imported, but still too high for my liking

Craig Slawson
Staffordshire Ecological Record

ser's Website

3 Reply by MikeWeideli

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

Re: Errors during Import

Craig

I think the Location problem is probably because the export gets the location related to the Location for the record being extracted, but not the parents for these related locations. The fact that you have no survey event or sample rejections suggests that the Location which has been rejected is not one used on the actual data.

The rejections on the Taxon and Biotope are possibly because at the moment Recorder is exporting user defined taxa and biotopes even though these can not be imported. It would more sense not to export these rather than to reject them at import and this is a modification which is being considered.

The rejection of the Individual is a bit of a puzzle and needs looking at a bit more carefully.  I suggest as a first step you check in the database whether or not  name key  SR00001100000103 exists and if it does whether or  not a corresponding entry exists  in Organisation or Individual.

Mike

Mike Weideli

MikeWeideli's Website

4 Reply by MikeWeideli

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

Re: Errors during Import

Craig

Sally has pointed out to me that the related locations do not get exported as I thought. The fact that related Locations are not exported will explain the rejection in Location Relations, but not the rejection of the Location. On the surface of it it looks as thought the parent for the exported Location is missing and the reason for this is not immediately apparent. To improve recorder and/or documentation in the future it would  be useful to understand the reason for all these rejections and I would be prepared to have a detailed look if you could provide the export file.

Mike

Mike Weideli

MikeWeideli's Website

5 Reply by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: Errors during Import

Mike,

Think I've worked out why the personal entry "SR00001100000103" created an error - the individual was linked to 3 organisations - perhaps one or more of those organisation was not exported - I need to check on the destination copy of Recorder when I get home

Craig Slawson
Staffordshire Ecological Record

ser's Website