1 Reply by LBacon

  • LBacon
  • Veteran
  • Offline
  • Registered: 12-09-2006
  • Posts: 80

Re: size limit on import, a bug or just our computers?

HI folks,

have just been running an import of around 151,000 records into a recorder6 stand-alone.  These were an NBN-zipped export from R2002 (on a different machine) where the records/locations are fine.

I left the machine running overnight, and found an "unhandled error" message, but thought little of it as apparently the recent training course in our office (I wasn't there) told us this does happen, nothing wrong.

So, to my delight, there were 150K+ records in the system ( but I admit I did not do the maths on exact numbers). Hurrah me thinks.

However, now that I have time to look at them, I wonder if there was a problem: 

Firstly, about half of the locations  have failed to appear, and the events are just given a grid reference, despite them having a location in R2K2.

Secondly, some events within the survey are empty

Thirdly, there is at least one instance of:
location A appearing twice, one entry fine, one entry having an additional site name (a different site about 1-2km away) - site B.  the description box and grid reference are that of site B
Site B then also appears, with its correct description & grid ref, with site A listed as an additional location name.

Any suggestions of what may have happened in any of these points most gratefully received.

the errors in the import file are many and fall into several categories:
Failed to insert SR00028500001JW9 into SQLSERVER_SAMPLE Database Error Message: ODBC--call failed.
Failed to insert SR00028500003CYG into SQLSERVER_TAXON_OCCURRENCE Database Error Message: ODBC--call failed.
Failed to insert SR00028500000EMC into SQLSERVER_SURVEY_EVENT_RECORDER Database Error Message: ODBC--call failed.
Failed to insert SR00028500001JHZ into SQLSERVER_TAXON_OCCURRENCE_DATA Database Error Message: ODBC--call failed.
Failed to insert SR00028500003V09 into SQLSERVER_TAXON_DETERMINATION Database Error Message: ODBC--call failed.
Failed to insert SR00028500007KK5 into SQLSERVER_TAXON_DETERMINATION Database Error Message:

Louise Bacon
Cambridgeshire & Peterborough Biological Records Centre

2 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: size limit on import, a bug or just our computers?

Hi Louise,

What version of Recorder 6 are you using?

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

3 Reply by LBacon

  • LBacon
  • Veteran
  • Offline
  • Registered: 12-09-2006
  • Posts: 80

Re: size limit on import, a bug or just our computers?

HI Charles - Its 6.7.2a standalone june 2006

LOuise

4 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: size limit on import, a bug or just our computers?

This could be caused by the CHANGED_DATE / ENTRY_DATE with a value of 00:00 problem. See this post for more info:

http://forums.nbn.org.uk/viewtopic.php?id=63

Have a look in the mdb within the zip file to see if the tables have values of 00:00 within the CHANGED_DATE or ENTRY_DATE fields. Seeing as you're having problems with locations in particular, I'd start there.

If it is indeed this that's causing the problem, the good news is that this issue has been fixed in the latest update. The bad news is that you'll need to remove the half-imported dataset before you re-import it. Are you able to restore your db from a recent backup?

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

5 Reply by LBacon

  • LBacon
  • Veteran
  • Offline
  • Registered: 12-09-2006
  • Posts: 80

Re: size limit on import, a bug or just our computers?

Hi Charles - have looked in the mbd file at dates, and there are filed with entered dates and sometimes changed dates assigned, as dd/mm/yyyy.  none of them have times attached.  Most have empty changed dates...

Any further ideas?

Louise

6 Reply by Alison Stewart

  • Alison Stewart
  • Member
  • Offline
  • Registered: 03-10-2006
  • Posts: 46

Re: size limit on import, a bug or just our computers?

I am adding this to this thread as it seems to be related.

Experimenting with the import wizard to import an Excel spreadsheet of just under 10,000 records, the process stops with a unhandled error message (see  Error 1 below).  Looking at the appropriate survey shows the events and samples have imported correctly, but not all of the taxon occurences.  On checking the Excel file to find any pattern to which rows had/had not imported, the only one I can find is that if the file is sorted into grid reference order the rows which have imported are in a block in the middle and the umimported rows are either side.

My first thought was that this could be some size limit factor.  However I have come across a further complication.  On cutting down the Excel file to just the rows that hadn't imported originally (about 3,500 records), the taxon occurences still didn't import if I was matching the species to the preferred checklists (slightly different error message, see Error 2 below), but it did complete when I matched them to only the old Recorder 3.3 list!  Unfortunately, matching the original larger file against the Recorder 3.3 list produces the same result as with the preferred checklists, so I still feel it is predominantly a size issue.

Any thoughts?


Alison Stewart
Environmental Database Manager
Dorset Environmental Records Centre


ERROR 1

Exception occurred in application Recorder 6 at 30/01/2007 22-53-01.
Version : 6.9.3.102

Exception path:
EOleException : ODBC--insert on a linked table 'SQLSERVER_TAXON_DETERMINATION' failed
EDBMergerError : Error occurred transferring records for SQLSERVER_TAXON_DETERMINATION into the main database
EDBMergerError : Error occurred transferring records for SQLSERVER_TAXON_DETERMINATION into the main database

Last event\actions:
  TfrmReferences deactivated
  TfrmImportWizard activated
  actDocuments invoked
  TfrmImportWizard deactivated
  TfrmReferences activated
  TfrmReferences activated
  actTransferData invoked
  TfrmReferences deactivated
  TfrmImportWizard activated

Operating System : Windows XP  5.01.2600  Service Pack 1
Physical Memory available : 515,440 KB

DLLs loaded:
  advapi32.dll (5.1.2600.1106)
  comctl32.dll (5.82.2800.1106)
  comdlg32.dll (6.0.2800.1106)
  gdi32.dll (5.1.2600.1106)
  HHCtrl.ocx (5.2.3735.0)
  kernel32.dll (5.1.2600.1106)
  mpr.dll (5.1.2600.0)
  MS5.Dll (5.0.0.12)
  MS5User.Dll (5.0.0.4)
  odbc32.dll (3.520.9042.0)
  ole32.dll (5.1.2600.1263)
  oleaut32.dll (3.50.5016.0)
  shell32.dll (6.0.2800.1233)
  user32.dll (5.1.2600.1255)
  version.dll (5.1.2600.0)
  winmm.dll (5.1.2600.1106)
  winspool.drv (5.1.2600.1106)

ERROR 2

Exception occurred in application Recorder 6 at 31/01/2007 23-15-59.
Version : 6.9.3.102

Exception path:
EOleException : ODBC--call failed
EDBMergerError : Error occurred transferring records for SQLSERVER_TAXON_OCCURRENCE into the main database

Last event\actions:
  TfrmReferences deactivated
  TfrmImportWizard activated
  actDocuments invoked
  TfrmImportWizard deactivated
  TfrmReferences activated
  TfrmReferences activated
  actTransferData invoked
  TfrmReferences deactivated
  TfrmImportWizard activated

Operating System : Windows XP  5.01.2600  Service Pack 1
Physical Memory available : 515,440 KB

DLLs loaded:
  advapi32.dll (5.1.2600.1106)
  comctl32.dll (5.82.2800.1106)
  comdlg32.dll (6.0.2800.1106)
  gdi32.dll (5.1.2600.1106)
  HHCtrl.ocx (5.2.3735.0)
  kernel32.dll (5.1.2600.1106)
  mpr.dll (5.1.2600.0)
  MS5.Dll (5.0.0.12)
  MS5User.Dll (5.0.0.4)
  odbc32.dll (3.520.9042.0)
  ole32.dll (5.1.2600.1263)
  oleaut32.dll (3.50.5016.0)
  shell32.dll (6.0.2800.1233)
  user32.dll (5.1.2600.1255)
  version.dll (5.1.2600.0)
  winmm.dll (5.1.2600.1106)
  winspool.drv (5.1.2600.1106)

Alison Stewart
Dorset Environmental Records Centre

7 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: size limit on import, a bug or just our computers?

Hi Alison

If possible can you email me the file? Probaby the smaller one is best and let me know which checklist you were originally trying to use to match against.

I'll give it a try on a machine here (will be tomorrow or next week though) and see if I come up against similar problems.

Many thanks,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Sarah Shaw
Biodiversity Information Assistant
JNCC

8 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: size limit on import, a bug or just our computers?

Hi

I was able to import the smaller file of Alison's (and match to a number of different checklists), however, I think this problem is related to the limitations of the import wizard, in terms of processing.

Louise - I think your problem is probably something else. I could also try giving your import a go, though perhaps the file is too large to email me?

I could give it a try here to see if this imports ok using my copy of Recorder 6, but won't be able to investigate in detail if it appears to be the items within the import file that are causing the problems.

For this, other than reviewing Stuart's documentation on common transfer problems - see this thread http://forums.nbn.org.uk/viewtopic.php?id=36), I would suggest contacting a reseller - they are very experienced in this area.

I'm not sure what the upper limit of the import wizard is, for example for excel files. I know it probably depends on a number of factors, such as how many different checklists used during matching etc.

Could anyone give any more information on the file size or how many rows they have managed to import successfully into Recorder 6 in this way?

Many thanks,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Sarah Shaw
Biodiversity Information Assistant
JNCC

9 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: size limit on import, a bug or just our computers?

I've not got any solid answers because it varies depending on what columns you have and the import method used. I tend to limit spreadsheet imports to about 10,000 rows which seems to be about the best tradeoff in terms of performance, stability and getting large numbers of records in. Any more than this and processing either gets stuck or takes a very, very long time for reasons that are unknown to me. For Access (ADO Conncection) based imports, about 20,000-25,000 rows are possible, but if there are a lot of locations to match, the location matching stage can get very unstable. I've spent several hours in the past matching locations, only to have the thing crash on me and I've had to start over again. These are difficult issues to report as they so random that they very difficult to reproduce with any reliability.

But again, this is all fairly anecdotal based on my own observations importing large quantities of very specific bird data, so I wouldn't quote it as canon. As they say across the pond, your mileage may vary.

A feature that would probably make life a lot easier and quicker when import wizarding would be a "search all preferred lists" at the species matching stage. I imagine it would increase processing time though.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

10 Reply by LBacon

  • LBacon
  • Veteran
  • Offline
  • Registered: 12-09-2006
  • Posts: 80

Re: size limit on import, a bug or just our computers?

HI Sarah - I could email the zipped files to you  (an NBN zipped export) - it is 11 MB, though.  Reading through this and various other posts, I suspect it may be the size of this survey (as originally mentioned, around 100, 000 records).  I intend to try again soon with both Recorder 2002 exported surveys and Excel files to see how I get on with say 10,000 records.  We have also been in contact with sally Rankin recently and will pick up on that with her soon.

Louise



Sarah Shaw wrote:

Hi

I was able to import the smaller file of Alison's (and match to a number of different checklists), however, I think this problem is related to the limitations of the import wizard, in terms of processing.

Louise - I think your problem is probably something else. I could also try giving your import a go, though perhaps the file is too large to email me?

I could give it a try here to see if this imports ok using my copy of Recorder 6, but won't be able to investigate in detail if it appears to be the items within the import file that are causing the problems.

For this, other than reviewing Stuart's documentation on common transfer problems - see this thread http://forums.nbn.org.uk/viewtopic.php?id=36), I would suggest contacting a reseller - they are very experienced in this area.

I'm not sure what the upper limit of the import wizard is, for example for excel files. I know it probably depends on a number of factors, such as how many different checklists used during matching etc.

Could anyone give any more information on the file size or how many rows they have managed to import successfully into Recorder 6 in this way?

Many thanks,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

11 Reply by Sarah Shaw

  • Sarah Shaw
  • Veteran
  • Offline
  • Registered: 23-06-2006
  • Posts: 267

Re: size limit on import, a bug or just our computers?

Hi Louise

I'm not sure what our limit is here, but you could try emailing me the zip file last thing before you leave in the evening - when the email traffic calms!


Many thanks,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Sarah Shaw
Biodiversity Information Assistant
JNCC

12 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: size limit on import, a bug or just our computers?

If you can't transmit files via email, try yousendit.com; we use it here all the time. You can read an article about this and other similar services on the Independent website if you want a little background info. I've found Yousendit.com to be the most professional service of this type.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

13 Reply by brianmiller

  • brianmiller
  • Veteran
  • Offline
  • Registered: 10-05-2006
  • Posts: 113

Re: size limit on import, a bug or just our computers?

Hi Sarah,

Into a blank 692 database I was only ever able to import about 4456 records via the import wizard of a variety of species data. I was not able to get it passed this figure even using an ADO connection to an Access database file. Whether there are differences in numbers (as highlighted by Charles) when you already have data in Recorder I do not know.

Hope this helps.

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

brianmiller's Website