1 Topic by SteveHannah

  • SteveHannah
  • Veteran
  • Offline
  • From: Midlothian
  • Registered: 02-09-2010
  • Posts: 84

Topic: Error with Create new determination based on spreadsheet batch update

I'm trying to run the above batch update on a set of records but when I do, this message keeps appearing.

There is an error in the SQL code.  The error message is: 'The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TAXON_DETERMINATION_TAXON_OCCURRENCE".  The conflict occurred in database "NBNData", table "dbo.TAXON_OCCURRENCE", column 'TAXON_OCCURRENCE_KEY"

I ran the batch update on a different set of records prior to this and it worked perfectly.  Any ideas?

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

SteveHannah's Website

2 Reply by MikeWeideli

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

Re: Error with Create new determination based on spreadsheet batch update

Looks like it is trying to add a determination when there is no occurrence key there for it. Could happen if you have deleted a record  after extracting the Taxon_Occurrence_keys. Apart from trial and error an xml report on the table could find the problem.

Mike Weideli

MikeWeideli's Website

3 Reply by SteveHannah

  • SteveHannah
  • Veteran
  • Offline
  • From: Midlothian
  • Registered: 02-09-2010
  • Posts: 84

Re: Error with Create new determination based on spreadsheet batch update

Managed to track down the error, and it wasn't with the update!  It's actually an odd quirk in Excel when saving as a csv file.  One of the TOK's was all numerical, until the last 4 characters, where it ended in E017 (or something like that), but it's that E that is causing the problem.  As it's all numerical, when saving as a csv, it assumed that the E was the notation for a long number and the numbers after it, the length.  Hence it rewrote the TOK as the first 3 digits of the TOK, plus the requisite number of zeroes.  It will change it to that automatically.

Does the batch update only work if the file is csv, or would it run if the update file was saved as an xlsx?

-----------------------------------------------------------------------
Steve Hannah - GIS and Data Infrastructure Officer
The Wildlife Information Centre

SteveHannah's Website