1 Reply by Richard Wright

  • Richard Wright
  • Member
  • Offline
  • Registered: 12-10-2011
  • Posts: 30

Re: Basic problems

Having successfully (I thought) installed Recorder 6 I tried to import my records produced by Mapmate using the Import wizard. After a fair bit of work, everything seemed to be going well until the last process when after working away for a fair while, everything ground to a halt, the progress bar stopped moving, no hard disk activity. I left it for hours, just in case something was happening in the background but eventually I was left with the hour glass and a "not responding" message. I had to shut it down with Ctrl-Alt-Del. On restarting Recorder, it appeared that about 90% of the species observations had  been successfully imported, though I had no idea of which had failed. I then tried to delete the survey, thinking to try again, but quickly got a message that the operation had failed. Then I tried to backup the database, but immediately received an unhandled error message. After a good deal more head scratching I eventually decided to uninstall and reinstall a "clean" copy of Recorder.

Having done this, and with only the supplied data in my database, I thought I'd try the backup operation again. Once more I received the unhandled error message and the following was in the Last Error file :

exception message : Cannot open backup device 'NBNData_Backup(C:\DOCUME~1\user\LOCALS~1\Temp\NBNBackup.bak)'. Operating system error 5(error not found).

I have :
Operating System : Windows XP  5.01.2600  Service Pack 3
SQL Server Express 2005 Service Pack 4 (downloaded from Microsoft)
77 Gb disk space available.
I upgraded Recorder to 6.16.241

I feel that there must be something basically wrong with the setup and would appreciate any suggestions before I bother to try importing my data again or doing anything else.

Richard Wright

2 Reply by Richard Wright

  • Richard Wright
  • Member
  • Offline
  • Registered: 12-10-2011
  • Posts: 30

Re: Basic problems

A further update on this. I loaded the trial data from the CD. This imported and all seems to work OK. But I still get the same error message when I try to backup the database.

Richard Wright

3 Reply by MikeWeideli

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

Re: Basic problems

The backup problem and the import issues are probably not related. Can you please supply a bit more information.

From your previous post I see you were trying to import 80,000 records.  What format were they in ?     Which method did you use to try and delete the Survey - the addin or the XML Batch Update ?

The Backup problem could be some of the security measures introuduced in XP SP3 by Microsoft. If you haven't done so can you try changing the backup location and let me know what happens. This is done through Tools/Database Tools/View or Change  backup Location.

Mike Weideli

MikeWeideli's Website

4 Reply by Richard Wright

  • Richard Wright
  • Member
  • Offline
  • Registered: 12-10-2011
  • Posts: 30

Re: Basic problems

Mike
The backup problem remains. I tried changing the backup location to another place on my C: drive and also to a USB drive that I generally use for backups, but no change in the error message.

The XML Batch Update did succeed in deleting the survey, it was the addin that failed.

I did more or less successfully complete the import. All of the species observations (86,721) were imported, but there was a message that some 6,000 plus invalid items were not imported. I thought that ImportRejects would tell me what they were but a dozen statements such as the following don't help me much, I'm afraid!:

Failed to insert SR00061400000EFW into SAMPLE
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_SURVEY_EVENT". The conflict occurred in database "NBNData", table "dbo.SURVEY_EVENT", column 'SURVEY_EVENT_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

I assume that this means that part of the data for some records was rejected, which is unlikely to be a problem.

I wonder if my original problem with the import was because I was doing several other things on the computer at the same time I was waiting for it to complete. It's probably better to begin fairly large database operations when you can leave it to get on with things undisturbed.

Richard Wright

5 Reply by Richard Wright

  • Richard Wright
  • Member
  • Offline
  • Registered: 12-10-2011
  • Posts: 30

Re: Basic problems

I now realise that I made a major error. Instead of importing my locations column into "Location" I actually imported them to "Location Name". The result is that I don't actually have any locations, where there should be several hundred. I assume that the only way I will be able to deal with this is to delete the whole lot and repeat the import, unless there is some clever way of creating the locations from the names.

Richard Wright

6 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Basic problems

To resolve the backup problem you could try deleting the backup device and recreating it as specified in http://forums.nbn.org.uk/viewtopic.php?pid=9381#p9381. I know that was written for Windows 7 but there is a reasonable chance it will also work for XP.

If you get invalid items when you are importing data you need to use the Save Details button to save the details of them to a text file. You will then know what hasn’t been imported. Often it is best to save the details, cancel the import, correct the import file and try again. Provided you don’t lose your remembered matches, it shouldn’t  take too long to redo the import. 

A lot of users use Location Name instead of Location when importing MapMate data because they simply don’t have the time and resources that are involved in the Location matching/creation. Using Location Name avoids this.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

7 Reply by Richard Wright

  • Richard Wright
  • Member
  • Offline
  • Registered: 12-10-2011
  • Posts: 30

Re: Basic problems

Sally
Thanks for your reply. I'll look at the backup problem again later.

I'm sure I selected the Save Details  button, but the only text file I could find was the ImportRejects file with the type of information I included in the previous post.

Unfortunately, correcting the import file isn't that simple. I don't have access to or control of all the data in Mapmate, and the tab text file is too large for Excel. I'm not sure if there is another way to edit the data. I suspect the rejected items are minor anyway, as all of the basic species/location name/recorder etc appear to be present. I must say that after all the various stages of validation, it seems strange that items are still rejected at the very end, particularly after I had waited for 5 hours while the records were imported! Why aren't these picked up at an earlier stage?

I really do need locations. I am very keen to get a proper site/subsite hierarchy sorted out, something which Mapmate lacks. I was hoping that all of my Mapmate sites would be imported as new locations in Recorder and that I could then edit them later to make some subsites of others.

Fortunately I have allowed myself six months to get all of this sorted out, and there's never anything to watch on TV anyway.

Thanks
Richard Wright

8 Reply by MikeWeideli

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

Re: Basic problems

Richard

If you like please upload the Import Rejects file to the forum or email it to me if it isn't too big and I will take a look for you, so at least you know why certain items are being rejected.

Mike Weideli

MikeWeideli's Website

9 Reply by Richard Wright

  • Richard Wright
  • Member
  • Offline
  • Registered: 12-10-2011
  • Posts: 30

Re: Basic problems

Mike
The following is the complete text in the ImportRejects File. Not a lot of it and meaningless to me.

Richard Wright

Items rejected by Recorder 6 in data imported at 12-20-49 on 19/10/2011:

Failed to insert SR00061400000EFW into SAMPLE
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_SURVEY_EVENT". The conflict occurred in database "NBNData", table "dbo.SURVEY_EVENT", column 'SURVEY_EVENT_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400000EYD into SAMPLE
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_SURVEY_EVENT". The conflict occurred in database "NBNData", table "dbo.SURVEY_EVENT", column 'SURVEY_EVENT_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400001X7M into TAXON_OCCURRENCE
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TAXON_OCCURRENCE_SAMPLE". The conflict occurred in database "NBNData", table "dbo.SAMPLE", column 'SAMPLE_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR000614000029WY into TAXON_OCCURRENCE
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TAXON_OCCURRENCE_SAMPLE". The conflict occurred in database "NBNData", table "dbo.SAMPLE", column 'SAMPLE_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400000BK7 into SURVEY_EVENT_RECORDER
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SURVEY_EVENT_RECORDER_SURVEY_EVENT". The conflict occurred in database "NBNData", table "dbo.SURVEY_EVENT", column 'SURVEY_EVENT_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400000DD6 into SURVEY_EVENT_RECORDER
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SURVEY_EVENT_RECORDER_SURVEY_EVENT". The conflict occurred in database "NBNData", table "dbo.SURVEY_EVENT", column 'SURVEY_EVENT_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400000EYD into SAMPLE_RECORDER
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_RECORDER_SAMPLE". The conflict occurred in database "NBNData", table "dbo.SAMPLE", column 'SAMPLE_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400000EFW into SAMPLE_RECORDER
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_RECORDER_SAMPLE". The conflict occurred in database "NBNData", table "dbo.SAMPLE", column 'SAMPLE_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400002CJ6 into TAXON_DETERMINATION
Database Error Message:
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'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400002436 into TAXON_DETERMINATION
Database Error Message:
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'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400003AR0 into TAXON_OCCURRENCE_DATA
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TAXON_OCCURRENCE_DATA_TAXON_OCCURRENCE". The conflict occurred in database "NBNData", table "dbo.TAXON_OCCURRENCE", column 'TAXON_OCCURRENCE_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

Failed to insert SR00061400003IQL into TAXON_OCCURRENCE_DATA
Database Error Message:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TAXON_OCCURRENCE_DATA_TAXON_OCCURRENCE". The conflict occurred in database "NBNData", table "dbo.TAXON_OCCURRENCE", column 'TAXON_OCCURRENCE_KEY'.(-2147217873) The statement has been terminated.(-2147217873)
Note: This record may have been rejected because of a parent record that was also rejected.
--------------------

10 Reply by MikeWeideli

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

Re: Basic problems

This is interesting because it highlights a problem with the rejection report which I was not aware of, but can duplicate. What appears to be happening is that one or possibly two Survey Events are being rejected and this is causing the Sample and other table entries down the structure to be rejected. What I would have expected to see is details of the rejected Survey Event, but this isn't being shown.  There aren't that many reasons for the rejection of a Survey Event which are not picked up at early stages in the wizard process. The most likely cause is a date which is before the allowed date you set up on the Survey.  For example if you have set up 1/1/1900 as the Records Allowed from  date  and the MapMate data contains a date earlier than this then the Event would have been rejected at the final import stage.

Assuming the above is the complete report  you have lost just two records. In order to identify these you probably need to look at the MapMate data or to run some reports on the MapMate system looking for the earliest dates.

The rejection report file goes by default into the ImportRejects.txt in the User File folder of Recorder 6.

Mike Weideli

MikeWeideli's Website

11 Reply by Richard Wright

  • Richard Wright
  • Member
  • Offline
  • Registered: 12-10-2011
  • Posts: 30

Re: Basic problems

Mike
Thanks for the reply. The error was caused by two date problems in the Mapmate import where the person entering the date had intended to put "11" to represent 2011, but had actually put "111" which put the date outside the survey range. I can correct these.

In fact I intend to delete this survey and perform the import again, making a few changes. In particular, I want to make the Mapmate Site Name correspond to Location in Recorder rather than Location Name. Am I correct in assuming that Recorder will create a new location for each Mapmate site, and that I will later be able to edit these to make some of them into subsites? This is important as Mapmate makes every new grid reference into a new site and I want to combine them into a more organised system.

Thanks
Richard Wright

12 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Basic problems

If you specify a column type of Location (not Location Name) when importing data using the import wizard it will give you a list of all the locations in the import file and you will need to specify what is to be done for each one although you will have the option to ‘Make New Entries’ for all of them – this may take some time for a lot of locations. The alternative is to go through the list and use ‘Make New Entry’ for all the locations you want in your location hierarchy and to tick ‘Place any remaining unmatched locations into location name’. If any locations have multiple grid references you will need to specify which one is to be used as the Central Spatial Ref. unless you tick ‘Use the first listed grid reference when making new entry’. For more detail see the Help: Contents tab – Tasks – Exchanging data – Import data – Import Wizard – Matching data. You will be able to move locations in the location hierarchy so that they become sub-sites of another location.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

13 Reply by Richard Wright

  • Richard Wright
  • Member
  • Offline
  • Registered: 12-10-2011
  • Posts: 30

Re: Basic problems

Sally
Thanks for that. I've actually decided to create properly organised Locations for all of the main sites (about 100) before I repeat the Mapmate import. I should then be able to match the relevant imported  locations to these then use, "Place any remaining unmatched locations into location name" for the remainder, which are mainly sites with a small number of records.
Richard Wright