1

Topic: Import Reject - Taxon Occurrence only

Hello All,

We had the following error while importing over 20,000 records in Recorder:

reated SQLSERVER_SURVEY_EVENT
Created SQLSERVER_SAMPLE
Created SQLSERVER_SURVEY_EVENT_RECORDER
Created SQLSERVER_TAXON_OCCURRENCE
Created SQLSERVER_TAXON_OCCURRENCE_SOURCES
Created SQLSERVER_TAXON_DETERMINATION
Created SQLSERVER_TAXON_OCCURRENCE_DATA

Failed to insert SR0003820000ZKVQ into TAXON_OCCURRENCE
Database Error Message:
The occurrence record and all related data were not imported as the determination record is missing or invalid.
--------------------

Failed to insert SR0003820000ZTSN into TAXON_OCCURRENCE
Database Error Message:
The occurrence record and all related data were not imported as the determination record is missing or invalid.
--------------------

Failed to insert SR0003820000ZFHS into TAXON_OCCURRENCE
Database Error Message:
The occurrence record and all related data were not imported as the determination record is missing or invalid.
--------------------

Failed to insert SR0003820000ZHRQ into TAXON_OCCURRENCE
Database Error Message:
The occurrence record and all related data were not imported as the determination record is missing or invalid.
--------------------
etc. etc

Only the event and sample were created and the taxon failed to be imported.

The over 20,000 events and samples were imported in a survey that has over 410,000 records.

My question is how will we be able to delete only the event and sample for these 20,000 records where there are no taxa?
Can anything be done on the front face of Recorder to delete all these data simultaneously? Or is there a report to run at the back tables?

I would hope there is a report that we can run such as "delete all data by X user imported on 09/01/2020".

Many thanks,
Agni

2

Re: Import Reject - Taxon Occurrence only

Hello All,

I was able to run a batch update to delete all these over 20,000 samples that have no taxon and that worked however when I run the batch update to delete all the events not having a sample I get the following error:

"There is an error in the SQL code. The error message is : The DELETE statement conflicted with the REFERENCE constraint "FK_SAMPLE_RECORDER_SURVEY_EVENT_RECORDER". The conflict occurred in database "NBNData", table "dbo.SAMPLE_RECORDER", column 'SE_RECORDER_KEY".

Any suggestions how we can correct this error in the SQL code?

Many thanks,
Agni

3

Re: Import Reject - Taxon Occurrence only

Is this on 6.30? Have you managed a successful import? I've just tried an import (thankfully, a small one) and am getting

Items rejected by Recorder 6 in data imported at 12-27-54 on 20/01/2020:
Created SQLSERVER_SURVEY_EVENT
Created SQLSERVER_SAMPLE
Created SQLSERVER_SURVEY_EVENT_RECORDER
Created SQLSERVER_TAXON_OCCURRENCE
Created SQLSERVER_TAXON_OCCURRENCE_DATA
Exception on create for SQLSERVER_TAXON_DETERMINATION: ODBC--call failed

Failed to insert SR00035100095H96 into TAXON_DETERMINATION
Database Error Message: 
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TAXON_DETERMINATION_DETERMINATION_TYPE". The conflict occurred in database "R6_LERC1", table "dbo.DETERMINATION_TYPE", column 'DETERMINATION_TYPE_KEY'.(-2147217873) 
--------------------
...

This looks to be due to the fact that DETERMINATION_TYPE column (in the access database that gets created) is populated with

Preferred Taxa

rather than a valid determination type key. A workaround is to specify the determination type in the import wizard.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

4 (edited by charliebarnes 21-01-2020 11:56:05)

Re: Import Reject - Taxon Occurrence only

Ah, I just realized this is probably because the default determination types aren't set in settings. However, trying to set them results in Recorder 6 hanging.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

5

Re: Import Reject - Taxon Occurrence only

Hi Charlie,

Sorry I should have written the version that we have that is 6.23.

We imported the over 20,000 records successfully after this error to a new survey.

I am still getting the same error in the SQL code when I try to run a batch update to delete the empty events.

Thanks,
Agni

6

Re: Import Reject - Taxon Occurrence only

Thanks Agni, hopefully missing determination types were just a problem with my upgrade.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

7

Re: Import Reject - Taxon Occurrence only

Hi Agni

The problem is undeleted Sample Recorder entries. If they are there then the related samples must still also exist, even if they are no longer viisble.  The Batch Update 'Del4 Delete Samples with no Occurrences' should have deleted these and I can't see anything in the Batch Update which would stop this from happening.

Please can you trying running  'Del4 Delete Samples with no Occurrences' again.

If this doesn't help and we don't fully understand how the situation has arisen it would probably be be best to run a report to identify what is there, before doing a special batch update to delete the entries.

Mike Weideli
Littlefield Consultancy - IT Consultants

8

Re: Import Reject - Taxon Occurrence only

Hi Charlie

The default determination type are held in the regsitry 'Settings' (HKEY CURRENT USER /Software/Dorset Softwar/Recorder 6/Settings). R6  should  automatically create these entries if they are not there, using the default of 'Unconfirmed'.  This part of the registry should be accesible to all users as it is where day to day information is stored (eg the last Window opened, last dictionary used etc.). Changing the option for default determination type should write these entries and if R6 is hanging when this is attempted it suggests that the registry can't be written to for some reason?

Mike Weideli
Littlefield Consultancy - IT Consultants

9

Re: Import Reject - Taxon Occurrence only

MikeWeideli wrote:

R6  should  automatically create these entries if they are not there, using the default of 'Unconfirmed'.

This was populated with 'Preferred Taxa' for me, but given my other issues I wouldn't worry about it unless anyone else reports it.

if R6 is hanging when this is attempted it suggests that the registry can't be written to for some reason?

This procedure is causing the hang: [dbo].[usp_Setting_Temp_Survey] (vague recollection this has happened before?)

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

10

Re: Import Reject - Taxon Occurrence only

Many thanks Mike,

I have run again the 'Del4 Delete Samples with no Occurrences' and it came up as completed and when I run then the 'Del3 Delete Events with No Samples' to delete the 20,000+ empty events I get the same error as above.

Can this error be linked to this one: https://forums.nbn.org.uk/viewtopic.php?id=7400 ?

We have 8 historical events that cannot delete manually or by running the batch update.
Would you advise to find these 8 events by the unique record identifier and delete them at the back tables of Recorder? And in which table(s) can we locate them?

11

Re: Import Reject - Taxon Occurrence only

Highly likely that this is nothing tp do with the 20,000 records, but something historic. Someone in the past may have attempted to manually move Sample Recoprders which  can leave Survey Event Recorder with incorrect links. Can you try running Report Sy06, from the System Supplied Problem menu as a first step. I will do you a special report to look for this particular error.

Mike Weideli
Littlefield Consultancy - IT Consultants

12

Re: Import Reject - Taxon Occurrence only

Attached is a report which will identify if you have any issues around Survey_Event_Recorder with corrupted links. If all is Ok in this respect it will not return any records. If it does then we can delete them with a batch update.

Post's attachments

LC_Special_SER_Issuse.xml 828 b, 1 downloads since 2020-01-24 

You don't have the permssions to download the attachments of this post.
Mike Weideli
Littlefield Consultancy - IT Consultants

13

Re: Import Reject - Taxon Occurrence only

Hi Charlie,

For v6.31 the stored procedure  which takes such a long time to run and 'hangs' R6 will only run if the Licence key for temp data changes and there will be a warning about the delay.

Mike Weideli
Littlefield Consultancy - IT Consultants

14

Re: Import Reject - Taxon Occurrence only

Many thanks Mike,

I will run this report.

15

Re: Import Reject - Taxon Occurrence only

Hi Mike,

This the result of Report Sy06:

Problem                                         Table                         Records
Missing Spatial_ref_System           Sample                            4.00
More than 1 Preferred           Taxon_Occurrence            22.00
No Accuracy                          Taxon_Occurrence            156.00
No Recorder                          Sample                             11.00
No Recorder                          Survey_Event                     26.00
Verified Indicator Wrong          Biotope_Occurrence            72.00
Verified Indicator Wrong         Taxon_Occurrence                  40136.00

(Not sure what happened with all these not verified records as I checked several by running Sy07 and all have determination type correct)

This is the result of the LC_Special_SER_Issuse report:

Sample_Key    Survey_EVent_Key
SR0004650000002W    SR0004650000002S
SR0004650000003W    SR0004650000003S
SR00046500000047    SR00046500000043
SR00046500000046    SR00046500000042
SR0004650000003K    SR0004650000003G
SR00046500000039    SR00046500000035
SR0004650000002D    SR00046500000029
SR0004650000001R    SR0004650000001S
SR00046500000028    SR00046500000024
SR00046500000025    SR00046500000026
SR00046500000021    SR0004650000001X
SR0004650000001V    SR0004650000001R
SR0004650000006A    SR0004650000006B
SR0004650000006F    SR0004650000006G
SR00046500000065    SR00046500000061
SR0004650000006V    SR0004650000006W
SR00046500000079    SR00046500000075
SR0004650000007R    SR0004650000007S
SR0004650000007L    SR0004650000007M
SR0004650000000U    SR0004650000000V
SR0004650000003C    SR00046500000038
SR00046500000043    SR0004650000003Z
SR0004650000003Q    SR0004650000003M
SR0004650000002I    SR0004650000002J
SR0004650000002M    SR0004650000002I
SR00046500000036    SR00046500000032
SR00046500000037    SR00046500000033
SR0004650000002X    SR0004650000002Y
SR0004650000001T    SR0004650000001P
SR0004650000001Z    SR0004650000001V
SR00046500000027    SR00046500000023
SR00046500000029    SR0004650000002A
SR0004650000001W    SR0004650000001S
SR00046500000049    SR00046500000045
SR00046500000056    SR00046500000052
SR0004650000005X    SR0004650000005T
SR00046500000060    SR0004650000005W
SR0004650000007D    SR0004650000007E
SR00046500000070    SR0004650000006W
SR0004650000007J    SR0004650000007F
SR0004650000007Q    SR0004650000007R
SR0004650000007N    SR0004650000007J
SR0004650000007U    SR0004650000007V
SR0004650000007H    SR0004650000007D
SR0004650000007Z    SR0004650000007V
SR00046500000081    SR0004650000007X
SR00046500000008    SR00046500000009
SR0004650000000O    SR0004650000000P
SR0004650000000P    SR0004650000000Q
SR0004650000002J    SR0004650000002K
SR00046500000042    SR0004650000003Y
SR0004650000002Z    SR0004650000002V
SR0004650000002S    SR0004650000002T
SR0004650000002X    SR0004650000002Y
SR0004650000003F    SR0004650000003B
SR0004650000002Y    SR0004650000002U
SR0004650000001S    SR0004650000001O
SR0004650000001U    SR0004650000001Q
SR0004650000002J    SR0004650000002K
SR0004650000005V    SR0004650000005R
SR0004650000006G    SR0004650000006C
SR00046500000069    SR0004650000006A
SR0004650000006N    SR0004650000006O
SR0004650000006P    SR0004650000006L
SR0004650000006Y    SR0004650000006Z
SR0004650000007G    SR0004650000007H
SR0004650000007U    SR0004650000007V
SR0004650000007P    SR0004650000007L
SR0004650000007Q    SR0004650000007R
SR0004650000007W    SR0004650000007S
SR0004650000007Y    SR0004650000007U
SR00046500000081    SR0004650000007X
SR00046500000005    SR00046500000006
SR0004650000000R    SR0004650000000S
SR00046500000039    SR00046500000035
SR0004650000003Y    SR0004650000003U
SR0004650000003U    SR0004650000003Q
SR00046500000040    SR0004650000003W
SR00046500000049    SR00046500000045
SR0004650000003G    SR0004650000003C
SR00046500000025    SR00046500000026
SR0004650000002I    SR0004650000002J
SR00046500000062    SR00046500000063
SR00046500000064    SR00046500000060
SR0004650000005Q    SR0004650000005M
SR00046500000063    SR0004650000005Z
SR0004650000006Z    SR0004650000006V
SR00046500000077    SR00046500000073
SR0004650000005L    SR0004650000005M
SR00046500000003    SR00046500000004
SR0004650000003B    SR00046500000037
SR0004650000002R    SR0004650000002N
SR0004650000003T    SR0004650000003P
SR0004650000002H    SR0004650000002D
SR00046500000033    SR0004650000002Z
SR0004650000002U    SR0004650000002Q
SR0004650000001Q    SR0004650000001R
SR0004650000001Y    SR0004650000001U
SR0004650000004A    SR00046500000046
SR0004650000005M    SR0004650000005I
SR0004650000005O    SR0004650000005K
SR0004650000006L    SR0004650000006H
SR00046500000062    SR00046500000063
SR0004650000006D    SR00046500000069
SR0004650000006K    SR0004650000006L
SR00046500000067    SR00046500000063
SR00046500000075    SR00046500000071
SR0004650000007B    SR0004650000007C
SR00046500000076    SR00046500000072
SR0004650000007M    SR0004650000007I
SR0004650000007F    SR0004650000007B
SR0004650000007X    SR0004650000007T
SR00046500000007    SR00046500000008
SR0004650000000A    SR0004650000000A
SR0004650000000T    SR0004650000000U
SR0004650000002E    SR0004650000002A
SR0004650000003X    SR0004650000003T
SR0004650000003R    SR0004650000003N
SR0004650000003Z    SR0004650000003V
SR0004650000002L    SR0004650000002H
SR0004650000003H    SR0004650000003D
SR0004650000002B    SR00046500000027
SR00046500000026    SR00046500000022
SR0004650000005K    SR0004650000005L
SR0004650000000G    SR0004650000000H
SR0004650000000A    SR0004650000000A
SR0004650000000Y    SR0004650000000Z
SR0004650000005R    SR0004650000005N
SR00046500000061    SR00046500000062
SR0004650000006I    SR0004650000006E
SR0004650000006E    SR0004650000006A
SR00046500000078    SR00046500000074
SR0004650000007L    SR0004650000007M
SR0004650000002F    SR0004650000002B
SR0004650000002P    SR0004650000002L
SR0004650000003S    SR0004650000003O
SR00046500000045    SR00046500000041
SR00046500000041    SR0004650000003X
SR00046500000044    SR00046500000040
SR0004650000002K    SR0004650000002G
SR00046500000034    SR00046500000030
SR00046500000030    SR0004650000002W
SR00046500000032    SR0004650000002Y
SR0004650000002A    SR00046500000026
SR0004650000001X    SR0004650000001T
SR0004650000005T    SR0004650000005P
SR00046500000061    SR00046500000062
SR0004650000006H    SR0004650000006D
SR0004650000006M    SR0004650000006N
SR0004650000005L    SR0004650000005M
SR0004650000006T    SR0004650000006U
SR0004650000006V    SR0004650000006W
SR0004650000006O    SR0004650000006K
SR0004650000005P    SR0004650000005L
SR00046500000071    SR00046500000072
SR0004650000005S    SR0004650000005O
SR0004650000006X    SR0004650000006T
SR0004650000007S    SR0004650000007O
SR00046500000004    SR00046500000005
SR0004650000000A    SR0004650000000A
SR0004650000002S    SR0004650000002T
SR00046500000048    SR00046500000044
SR0004650000003N    SR0004650000003J
SR0004650000003L    SR0004650000003H
SR0004650000003M    SR0004650000003I
SR0004650000002G    SR0004650000002C
SR0004650000003I    SR0004650000003E
SR0004650000002V    SR0004650000002R
SR00046500000020    SR0004650000001W
SR00046500000023    SR0004650000001Z
SR00046500000022    SR0004650000001Y
SR00046500000049    SR00046500000045
SR0004650000005I    SR0004650000005E
SR0004650000005W    SR0004650000005S
SR0004650000006A    SR0004650000006B
SR0004650000006C    SR00046500000068
SR00046500000066    SR00046500000067
SR0004650000006K    SR0004650000006L
SR0004650000006R    SR0004650000006N
SR00046500000074    SR00046500000070
SR0004650000007K    SR0004650000007G
SR0004650000007R    SR0004650000007S
SR0004650000007O    SR0004650000007K
SR0004650000007T    SR0004650000007P
SR00046500000080    SR0004650000007W
SR00046500000006    SR00046500000007
SR0004650000000M    SR0004650000000N


Not sure if it is useful, I am listing below the identifiers of the 8 events that historically we cannot delete:

10/08/1983 - SP301655:

The unique record identifier is SR00046500000028.

This Event was imported from database SR000465.


03/06/1988 - SP316614:

The unique record identifier is SR00046500000002.

This Event was imported from database SR000465.


29/09/1988 - SP308648:

The unique record identifier is SR0004650000000E.

This Event was imported from database SR000465.


02/07/1993 - SP3165:

The unique record identifier is SR00046500000049.

This Event was imported from database SR000465.


24/07/1993 - SP3265:

The unique record identifier is SR0004650000001M.

This Event was imported from database SR000465.


31/05/1994 - SP308651:

The unique record identifier is SR0004650000001F.

This Event was imported from database SR000465.


22/08/2002 - SP317635:

The unique record identifier is SR0004650000000O.

This Event was imported from database SR000465.



02/03/2004 - SP305601:

The unique record identifier is SR0004650000000G.

This Event was imported from database SR000465.


Many thanks,
Agni

16

Re: Import Reject - Taxon Occurrence only

Hi Agni

I will double check what the problem report is doing with the verification indicators. It is easy to fix these any and most of the others issues. ' No Recorder on Sample  and  Survey_Event' are probably a result of the Sample/Survey Event issues. The best thing with these will be to delete the entries in Sample_Recorder. You can't see these in R6 and there is no accurate way of working out what the entries should be. This will still leave Samples and Events with no recorders. It is a bit tricky to deal with these,  but it is would be possible to generate entries with the Recorders as 'Unknown'.

Let me know if you want me to work out the fixes.

Mike Weideli
Littlefield Consultancy - IT Consultants

17

Re: Import Reject - Taxon Occurrence only

Hello Mike,

Yes please could you work out the fixes?
Do I understand right that you mean that the recorders linked to these empty events will be turned to unknown?
What happens as these recorder names linked to these empty events are correct recorders for thousands of other correct records in the system?
Would the names stay as they are after these fixes for the correct records?
I am sorry for my lack of knowledge as I have used only the front face of Recorder.

Many thanks,
Agni

18

Re: Import Reject - Taxon Occurrence only

Hello Mike,

I am sorry I have not asked in my previous message would there be a cost for preparing these fixes?

Many thanks,
Agni

19

Re: Import Reject - Taxon Occurrence only

Hi
I wasn't thinking of charging. I need to work it out carefully. This situation can't occur unless someone has made changes through the back end. It is one of the areas where mistakes are easily made and you can't easily tell exactly what has been done. Possibly the correct entries are there as well as the problem ones. If you have the goto key addin take a look at some of the events. You may find that they have sample recorders (In recorders under Sample at least one Recorder should have a tick next to them), or they may not.

Mike Weideli
Littlefield Consultancy - IT Consultants

20

Re: Import Reject - Taxon Occurrence only

Hello Mike,

Thank you for your help.
There are only events in the system thus not sure what you mean with locating the sample recorders. As far as I can see all the samples linked to these events have been deleted.
Please is there a way to post a screenshot of Recorder on here or can I email it to you?

Thanks,
Agni