1 Reply by ser

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

Re: Exporting locations

I have just tried to export a set of locations to NBN Access Database (zipped) and I get the following error - no ZIP file is created:

date/time         : 2010-09-02, 13:44:15, 828ms
computer name     : WS021
user name         : slawsoc
operating system  : Windows XP Service Pack 3 build 2600
system language   : English
system up time    : 5 hours 46 minutes
program up time   : 5 hours 36 minutes
processors        : 4x Intel(R) Core(TM)2 Quad CPU    Q8300  @ 2.50GHz
physical memory   : 2048/2048 MB (free/total)
free disk space   : (C:) 88.31 GB (R:) 11.08 GB
display mode      : 1920x1080, 32 bit
process id        : $a24
allocated memory  : 41.83 MB
command line      : RecorderApp.exe /721830
executable        : RecorderApp.exe
exec. date/time   : 2010-07-22 16:40
version           : 6.15.4.238
madExcept version : 2.7h
exception class   : EOleException
exception message : [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

main thread ($a28):
00566a83 RecorderApp.exe ADODB                TCustomADODataSet.OpenCursor
005249c9 RecorderApp.exe DB                   TDataSet.SetActive
00524814 RecorderApp.exe DB                   TDataSet.Open
0090ae41 RecorderApp.exe DataExport   373  +8 AddSubSiteKeys
0090ae86 RecorderApp.exe DataExport   378 +13 AddSubSiteKeys
0090afad RecorderApp.exe DataExport   394  +6 TdlgDataExport.PickupSubsites
0090acc0 RecorderApp.exe DataExport   343 +29 TdlgDataExport.bbOkClick
004cce08 RecorderApp.exe Controls             TControl.Click
004a9f5c RecorderApp.exe StdCtrls             TButton.Click
004aa050 RecorderApp.exe StdCtrls             TButton.CNCommand
004ccc70 RecorderApp.exe Controls             TControl.WndProc
004cfccf RecorderApp.exe Controls             TWinControl.WndProc
004a9e20 RecorderApp.exe StdCtrls             TButtonControl.WndProc
004cca40 RecorderApp.exe Controls             TControl.Perform
004cfe07 RecorderApp.exe Controls             DoControlMsg
004d045f RecorderApp.exe Controls             TWinControl.WMCommand
004b7a3c RecorderApp.exe Forms                TCustomForm.WMCommand
004ccc70 RecorderApp.exe Controls             TControl.WndProc
004cfccf RecorderApp.exe Controls             TWinControl.WndProc
004b5a8d RecorderApp.exe Forms                TCustomForm.WndProc
004cf94c RecorderApp.exe Controls             TWinControl.MainWndProc
00453460 RecorderApp.exe Classes              StdWndProc
7e4292de user32.dll                           SendMessageW
7e42a993 user32.dll                           CallWindowProcA
004cfdb3 RecorderApp.exe Controls             TWinControl.DefaultHandler
004cd210 RecorderApp.exe Controls             TControl.WMLButtonUp
004cd186 RecorderApp.exe Controls             TControl.WMMouseMove
004ccc70 RecorderApp.exe Controls             TControl.WndProc
004cfccf RecorderApp.exe Controls             TWinControl.WndProc
004a9e20 RecorderApp.exe StdCtrls             TButtonControl.WndProc
004cf94c RecorderApp.exe Controls             TWinControl.MainWndProc
00453460 RecorderApp.exe Classes              StdWndProc
7e4196c2 user32.dll                           DispatchMessageA
004bc12b RecorderApp.exe Forms                TApplication.ProcessMessage
004bc162 RecorderApp.exe Forms                TApplication.HandleMessage
004b8c71 RecorderApp.exe Forms                TCustomForm.ShowModal
00a0eba3 RecorderApp.exe FormActions 1144 +35 TdmFormActions.actExportExecute
004527ab RecorderApp.exe Classes              TBasicAction.Execute
004aea3d RecorderApp.exe ActnList             TContainedAction.Execute
004af6df RecorderApp.exe ActnList             TCustomAction.Execute
0045267f RecorderApp.exe Classes              TBasicActionLink.Execute
004c325f RecorderApp.exe Menus                TMenuItem.Click
004c4513 RecorderApp.exe Menus                TMenu.DispatchCommand
004c52ca RecorderApp.exe Menus                TPopupList.WndProc
004c5219 RecorderApp.exe Menus                TPopupList.MainWndProc
00453460 RecorderApp.exe Classes              StdWndProc
7e4196c2 user32.dll                           DispatchMessageA
004bc12b RecorderApp.exe Forms                TApplication.ProcessMessage
004bc162 RecorderApp.exe Forms                TApplication.HandleMessage
004bc392 RecorderApp.exe Forms                TApplication.Run
00a33a20 RecorderApp.exe RecorderApp  375 +83 initialization

thread $acc:
7c90e514 ntdll.dll                 KiFastSystemCallRet
7c90df48 ntdll.dll                 NtWaitForMultipleObjects
7c80958a kernel32.dll              WaitForMultipleObjectsEx
7e4195f3 user32.dll                MsgWaitForMultipleObjectsEx
7e4196a3 user32.dll                MsgWaitForMultipleObjects
0042aba7 RecorderApp.exe madExcept ThreadExceptFrame
>> created by main thread ($a28) at:
4ec67b7f gdiplus.dll               

thread $1c8:
7c90e514 ntdll.dll  KiFastSystemCallRet
7c90df48 ntdll.dll  NtWaitForMultipleObjects

thread $cc:
7c90e514 ntdll.dll     KiFastSystemCallRet
7c90df58 ntdll.dll     NtWaitForSingleObject
7c8025d5 kernel32.dll  WaitForSingleObjectEx
7c80253d kernel32.dll  WaitForSingleObject

thread $24c:
7c90e514 ntdll.dll  KiFastSystemCallRet
7c90d218 ntdll.dll  NtDelayExecution

thread $e48: <priority:15>
7c90e514 ntdll.dll     KiFastSystemCallRet
7c90df48 ntdll.dll     NtWaitForMultipleObjects
7c80958a kernel32.dll  WaitForMultipleObjectsEx
7c80a110 kernel32.dll  WaitForMultipleObjects

thread $498: <priority:2>
7c90e514 ntdll.dll                 KiFastSystemCallRet
7e427766 user32.dll                GetMessageA
0042aba7 RecorderApp.exe madExcept ThreadExceptFrame
>> created by thread $f90 at:
76b45e9f winmm.dll                 

thread $ca0:
7c90e514 ntdll.dll                 KiFastSystemCallRet
7c90daa8 ntdll.dll                 NtReplyWaitReceivePortEx
0042aba7 RecorderApp.exe madExcept ThreadExceptFrame
>> created by thread $344 at:
77e7df36 RPCRT4.dll               

thread $e00:
7c90e514 ntdll.dll                 KiFastSystemCallRet
7c90daa8 ntdll.dll                 NtReplyWaitReceivePortEx
0042aba7 RecorderApp.exe madExcept ThreadExceptFrame
>> created by thread $ca0 at:
77e7df36 RPCRT4.dll               

DatabaseOutput ($8f4):
7c90e514 ntdll.dll                                      KiFastSystemCallRet
7c90df58 ntdll.dll                                      NtWaitForSingleObject
71ab4d13 WS2_32.dll                                     WSARecv
71ad2e9e wsock32.dll                                    recv
6d4f1655 DBNETLIB.DLL                                   ConnectionRead
00407760 RecorderApp.exe System                         @IntfClear
00565c1e RecorderApp.exe ADODB                          TADOCommand.Execute
0056dfd7 RecorderApp.exe ADODB                          TADOQuery.ExecSQL
0063b7e4 RecorderApp.exe DatabaseAccessADO      941  +8 TdmDatabase.ExecuteSQLGetRowsAffected
008f4962 RecorderApp.exe DatabaseOutput         437 +14 TDatabaseOutput.CopyJoinedRecords
008f8049 RecorderApp.exe DatabaseOutput         945  +4 TDatabaseOutput.GetObservations
008f6b99 RecorderApp.exe DatabaseOutput         654 +27 TDatabaseOutput.Execute
008f8f92 RecorderApp.exe ThreadedDatabaseOutput 157 +10 TThreadedDatabaseOutput.Execute
0042ac12 RecorderApp.exe madExcept                      HookedTThreadExecute
00451820 RecorderApp.exe Classes                        ThreadProc
00405470 RecorderApp.exe System                         ThreadWrapper
0042aba7 RecorderApp.exe madExcept                      ThreadExceptFrame
>> created by main thread ($a28) at:
008f8d22 RecorderApp.exe ThreadedDatabaseOutput 100  +1 TThreadedDatabaseOutput.Create

thread $5e0:
7c90e514 ntdll.dll                 KiFastSystemCallRet
7e4191ec user32.dll                GetMessageW
0042aba7 RecorderApp.exe madExcept ThreadExceptFrame
>> created by DatabaseOutput ($8f4) at:
7752fd64 ole32.dll                 

modules:
00340000 C4DLL.DLL         1.0.0.1          R:
003b0000 MS5User.Dll       5.0.0.4          R:
00400000 RecorderApp.exe   6.15.4.238       R:
01430000 ms5cnv.dll        5.0.0.10         R:
01c40000 odbcint.dll       3.525.1132.0     C:\WINDOWS\system32
02aa0000 xpsp2res.dll      5.1.2600.5512    C:\WINDOWS\system32
03ba0000 SQLOLEDB.RLL      2000.85.1132.0   C:\Program Files\Common Files\System\Ole DB
03bb0000 msadcer.dll       2.81.1132.0      C:\Program Files\Common Files\System\msadc
03ef0000 GoToKey.ocx       6.0.1.10         R:\Addins
04130000 NBNExchange.ocx   6.13.2.24        R:\Addins
04380000 Phenology.ocx     6.0.1.10         R:\Addins
046a0000 ShowUnchecked.ocx 6.0.0.13         R:\Addins
048d0000 StdValLib.dll     6.15.1.230       R:\Addins
04ad0000 SurveyDelete.ocx  6.0.0.29         R:\Addins
050a0000 Normaliz.dll      6.0.5441.0       C:\WINDOWS\system32
052e0000 PDFShell.dll      9.3.3.177        C:\Program Files\Common Files\Adobe\Acrobat\ActiveX
06300000 MSVCR80.dll       8.0.50727.4053   C:\WINDOWS\WinSxS\x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.4053_x-ww_e6967989
063b0000 nvcpl.dll         6.14.11.8250     C:\WINDOWS\system32
07120000 NVRSENG.DLL       6.14.11.8250     C:\WINDOWS\system32
07160000 nvshell.dll       6.14.10.12095    C:\WINDOWS\system32
08060000 msls31.dll        3.10.349.0       C:\WINDOWS\system32
10000000 MS5.Dll           5.0.0.12         R:
1b000000 ImgUtil.dll       8.0.6001.18702   C:\WINDOWS\system32
20000000 xpsp3res.dll      5.1.2600.5512    C:\WINDOWS\system32
3cea0000 mshtml.dll        8.0.6001.18939   C:\WINDOWS\system32
3d7a0000 jscript.dll       5.8.6001.22960   C:\WINDOWS\system32
3d930000 WININET.dll       8.0.6001.18939   C:\WINDOWS\system32
3dfd0000 iertutil.dll      8.0.6001.18939   C:\WINDOWS\system32
3e1c0000 ieframe.dll       8.0.6001.18939   C:\WINDOWS\system32
4dd00000 sqloledb.dll      2000.85.1132.0   C:\Program Files\Common Files\System\Ole DB
4de10000 msado15.dll       2.81.1132.0      C:\Program Files\Common Files\System\ado
4ec50000 gdiplus.dll       5.2.6001.22319   C:\WINDOWS\WinSxS\x86_Microsoft.Windows.GdiPlus_6595b64144ccf1df_1.0.6001.22319_x-ww_f0b4c2df
5ad70000 uxtheme.dll       6.0.2900.5512    C:\WINDOWS\system32
5b860000 NETAPI32.dll      5.1.2600.5694    C:\WINDOWS\system32
5d090000 comctl32.dll      5.82.2900.5512   C:\WINDOWS\system32
5edd0000 olepro32.dll      5.1.2600.5512    C:\WINDOWS\system32
605d0000 mslbui.dll        5.1.2600.5512    C:\WINDOWS\system32
60e30000 MSDATL3.dll       2.81.1132.0      C:\Program Files\Common Files\System\Ole DB
65e20000 itss.dll          5.2.3790.4186    C:\WINDOWS\system32
662b0000 hnetcfg.dll       5.1.2600.5512    C:\WINDOWS\system32
67080000 fzshellext.dll    3.1.0.1          C:\Program Files\FileZilla FTP Client
68000000 rsaenh.dll        5.1.2600.5507    C:\WINDOWS\system32
68100000 dssenh.dll        5.1.2600.5507    C:\WINDOWS\system32
69b10000 msxml4.dll        4.20.9876.0      c:\WINDOWS\system32
6d4f0000 DBNETLIB.DLL      2000.85.1132.0   C:\WINDOWS\system32
71800000 shdoclc.dll       6.0.2900.5512    C:\WINDOWS\system32
71a50000 mswsock.dll       5.1.2600.5625    C:\WINDOWS\system32
71a90000 wshtcpip.dll      5.1.2600.5512    C:\WINDOWS\System32
71aa0000 WS2HELP.dll       5.1.2600.5512    C:\WINDOWS\system32
71ab0000 WS2_32.dll        5.1.2600.5512    C:\WINDOWS\system32
71ad0000 wsock32.dll       5.1.2600.5512    C:\WINDOWS\system32
71b20000 mpr.dll           5.1.2600.5512    C:\WINDOWS\system32
71bf0000 SAMLIB.dll        5.1.2600.5512    C:\WINDOWS\System32
71c10000 ntlanman.dll      5.1.2600.5512    C:\WINDOWS\System32
71c80000 NETRAP.dll        5.1.2600.5512    C:\WINDOWS\System32
71c90000 NETUI1.dll        5.1.2600.5512    C:\WINDOWS\System32
71cd0000 NETUI0.dll        5.1.2600.5512    C:\WINDOWS\System32
71f80000 security.dll      5.1.2600.5512    C:\WINDOWS\system32
72410000 mydocs.dll        6.0.2900.5512    C:\WINDOWS\system32
72d10000 msacm32.drv       5.1.2600.0       C:\WINDOWS\system32
72d20000 wdmaud.drv        5.1.2600.5512    C:\WINDOWS\system32
73000000 winspool.drv      5.1.2600.5512    C:\WINDOWS\system32
73160000 OLEDB32.DLL       2.81.1132.0      C:\Program Files\Common Files\System\OLE DB
732e0000 RICHED32.DLL      5.1.2600.0       C:\WINDOWS\system32
74060000 msadce.dll        2.81.3002.0      C:\Program Files\Common Files\System\msadc
74320000 odbc32.dll        3.525.1132.0     C:\WINDOWS\system32
746f0000 msimtf.dll        5.1.2600.5512    C:\WINDOWS\system32
74720000 MSCTF.dll         5.1.2600.5512    C:\WINDOWS\system32
74c80000 OLEACC.dll        4.2.5406.0       C:\WINDOWS\system32
74e30000 RICHED20.dll      5.30.23.1230     C:\WINDOWS\system32
750b0000 RESUTILS.DLL      5.1.2600.5512    C:\WINDOWS\system32
750f0000 MTXCLU.DLL        2001.12.4414.706 C:\WINDOWS\system32
75130000 colbact.DLL       2001.12.4414.700 C:\WINDOWS\system32
75350000 OLEDB32R.DLL      2.81.1132.0      C:\Program Files\Common Files\System\OLE DB
754d0000 CRYPTUI.dll       5.131.2600.5512  C:\WINDOWS\system32
755c0000 msctfime.ime      5.1.2600.5512    C:\WINDOWS\system32
75cf0000 MLANG.dll         6.0.2900.5512    C:\WINDOWS\system32
75f60000 drprov.dll        5.1.2600.5512    C:\WINDOWS\System32
75f70000 davclnt.dll       5.1.2600.5512    C:\WINDOWS\System32
75f80000 browseui.dll      6.0.2900.5512    C:\WINDOWS\system32
76080000 MSVCP60.dll       6.2.3104.0       C:\WINDOWS\system32
76380000 msimg32.dll       5.1.2600.5512    C:\WINDOWS\system32
76390000 IMM32.DLL         5.1.2600.5512    C:\WINDOWS\system32
763b0000 comdlg32.dll      6.0.2900.5512    C:\WINDOWS\system32
765b0000 MSDART.DLL        2.81.1132.0      C:\WINDOWS\system32
76600000 CSCDLL.dll        5.1.2600.5512    C:\WINDOWS\System32
76620000 comsvcs.dll       2001.12.4414.702 C:\WINDOWS\system32
76780000 SHFolder.dll      6.0.2900.5512    C:\WINDOWS\system32
76790000 cryptdll.dll      5.1.2600.5512    C:\WINDOWS\system32
767a0000 ntdsapi.dll       5.1.2600.5512    C:\WINDOWS\system32
767f0000 schannel.dll      5.1.2600.6006    C:\WINDOWS\system32
76980000 LINKINFO.dll      5.1.2600.5512    C:\WINDOWS\system32
76990000 ntshrui.dll       5.1.2600.5512    C:\WINDOWS\system32
769c0000 USERENV.dll       5.1.2600.5512    C:\WINDOWS\system32
76b20000 ATL.DLL           3.5.2284.2       C:\WINDOWS\system32
76b40000 winmm.dll         5.1.2600.5512    C:\WINDOWS\system32
76bf0000 PSAPI.DLL         5.1.2600.5512    C:\WINDOWS\system32
76c30000 WINTRUST.dll      5.131.2600.5922  C:\WINDOWS\system32
76c90000 IMAGEHLP.dll      5.1.2600.5512    C:\WINDOWS\system32
76d10000 CLUSAPI.DLL       5.1.2600.5512    C:\WINDOWS\system32
76d60000 iphlpapi.dll      5.1.2600.5512    C:\WINDOWS\system32
76f20000 DNSAPI.dll        5.1.2600.5625    C:\WINDOWS\system32
76f60000 WLDAP32.dll       5.1.2600.5512    C:\WINDOWS\system32
76fb0000 winrnr.dll        5.1.2600.5512    C:\WINDOWS\System32
76fc0000 rasadhlp.dll      5.1.2600.5512    C:\WINDOWS\system32
76fd0000 CLBCATQ.DLL       2001.12.4414.700 C:\WINDOWS\system32
77050000 COMRes.dll        2001.12.4414.700 C:\WINDOWS\system32
77120000 oleaut32.dll      5.1.2600.5512    C:\WINDOWS\system32
773d0000 comctl32.dll      6.0.2900.5512    C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.5512_x-ww_35d4ce83
774e0000 ole32.dll         5.1.2600.5512    C:\WINDOWS\system32
77690000 NTMARTA.DLL       5.1.2600.5512    C:\WINDOWS\system32
77920000 SETUPAPI.dll      5.1.2600.5512    C:\WINDOWS\system32
77a20000 cscui.dll         5.1.2600.5512    C:\WINDOWS\System32
77a80000 crypt32.dll       5.131.2600.5512  C:\WINDOWS\system32
77b20000 MSASN1.dll        5.1.2600.5875    C:\WINDOWS\system32
77b40000 appHelp.dll       5.1.2600.5512    C:\WINDOWS\system32
77bd0000 midimap.dll       5.1.2600.5512    C:\WINDOWS\system32
77be0000 MSACM32.dll       5.1.2600.5512    C:\WINDOWS\system32
77c00000 version.dll       5.1.2600.5512    C:\WINDOWS\system32
77c10000 msvcrt.dll        7.0.2600.5512    C:\WINDOWS\system32
77c70000 msv1_0.dll        5.1.2600.5876    C:\WINDOWS\system32
77dd0000 advapi32.dll      5.1.2600.5755    C:\WINDOWS\system32
77e70000 RPCRT4.dll        5.1.2600.5795    C:\WINDOWS\system32
77f10000 GDI32.dll         5.1.2600.5698    C:\WINDOWS\system32
77f60000 SHLWAPI.dll       6.0.2900.5912    C:\WINDOWS\system32
77fe0000 Secur32.dll       5.1.2600.5834    C:\WINDOWS\system32
78130000 urlmon.dll        8.0.6001.18939   C:\WINDOWS\system32
7c800000 kernel32.dll      5.1.2600.5781    C:\WINDOWS\system32
7c900000 ntdll.dll         5.1.2600.5755    C:\WINDOWS\system32
7c9c0000 shell32.dll       6.0.2900.6018    C:\WINDOWS\system32
7e290000 shdocvw.dll       6.0.2900.5897    C:\WINDOWS\system32
7e410000 user32.dll        5.1.2600.5512    C:\WINDOWS\system32
7e4b0000 HHCtrl.ocx        5.2.3790.4110    C:\WINDOWS\system32
7e720000 SXS.DLL           5.1.2600.5512    C:\WINDOWS\system32

Craig Slawson
Staffordshire Ecological Record

ser's Website

2 Reply by ser

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

Re: Exporting locations

Further tests have revealed I can export just a single site without any problems, so I'm assuming there is a database problem - a "Data Validation" check reported no problems, so I'm not sure where the actual problem lies

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: Exporting locations

Craig

In the upload area of the forum is a file problems.zip. This contains two XML reports, a batch update and some explanatory text. The XML reports pick up a number of errors which can occur in data  which are not picked up by standard validation.  It may pick up what is causing your problem.


Mike

Mike Weideli

MikeWeideli's Website

4 Reply by sallyrankin

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

Re: Exporting locations

The way you export multiple locations, names and addresses and documents is to put them in a rucksack and use that to drive the export. Open the rucksack before selecting Tools - Export Data and the system will automatically export all the items in the open rucksack. When exporting these things you will be offered the choice of exporting them with or without the observations linked to them - see Help - Contents tab - Tasks - Exchanging data - Export NBN data and see the note on exporting data for multiple locations etc. When exporting locations you will also be offered the option to include subsites so to export all your locations you only need the top level locations in your rucksack.

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

5 Reply by ser

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

Re: Exporting locations

I have run Mike's problem searching routine, which picked up 100s of errors, mainly due to duplicate preferred determinations for species (Grrrr), but absolutely no errors found in the Locations table, so still don't know what caused the failure

Craig Slawson
Staffordshire Ecological Record

ser's Website

6 Reply by MikeWeideli

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

Re: Exporting locations

Craig

At the moment I  can't get the location export to fails even on systems which are full of  other errors. I will try a few more databases and if I can duplicate the problem I will investigate further. 

The system supplied  Batch Update (Update-Pref1) does fix duplicate preferred, but it has to decide which one to keep as preferred  and the rules applied may not be to everyones liking.

Mike

Mike Weideli

MikeWeideli's Website

7 Reply by ser

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

Re: Exporting locations

I have made all the corrections reported by Mike's XML routines and re-run the export Site - guess what - went through without any error! I don't believe the correction made any difference as none had anything to do with locations - I think it must have been a connection glitch in the network, hence the "SQL Server does not exist or access denied" might have been telling the truth for a change - can't explain it any other way, but Mike, thanks for looking into it!

Craig Slawson
Staffordshire Ecological Record

ser's Website

8 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Exporting locations

I have just run Mike's summary query and there are 184458 Verified Indicator Wrong errors (out of a database of 245000), presumably down to the transfer from R3 as mentioned in the document. What will correcting this mean for the database?

Gordon Barker
Biological Survey Data Manager
National Trust

9 Reply by MikeWeideli

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

Re: Exporting locations

If the indicators are 0 (not verified) instead of 2 (passed verification) or vice versa the difference to your database will be mainly cosmetic   This will probably  be the case if the problems come from  an Recorder 3 transfer.  If  the indicator on an occurence  is  set  to 1  (Failed/pending verification), it will not appear in reports unless you specifically indicate that these records should be included (Report Wizard – Additional filters – Constraints), and it won't export,  except via a Recording Scheme.  So if this is wrong it could cause a problem.

Either way it is s good idea to keep the verified indicators in line with the Determination types so that the validation flag dislayed on the General tab of the taxon or biotope observation is correct.



Mike

Mike Weideli

MikeWeideli's Website

10 Reply by ser (edited by ser 07-09-2010 12:38:49)

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

Re: Exporting locations

Gordon,

Here is a full list of the determination and its associated verification value, we had the same problem - almost wholly due to the transfer from Rec3 to Rec6 - we had 329,000 to correct!

Invalid    1
Validation    2
Observation    0
Original    0
Confirmation    2
Considered Correct    2
Considered Incorrect    1
Incorrect    1
Requires Confirmation    1
Unconfirmed    0
Correct    2

Craig Slawson
Staffordshire Ecological Record

ser's Website

11 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Exporting locations

OK so checking one of the records it has a Det type of Observation and has Passed Verification in the box, but this should be Not verified and the xml will correct this? This will probably apply to all the records that came across from R3.

I haven't really paid any attention to determination type or verification values so far and this tool appears not to make important changes to these records but I will have to run it to correct the other problems. Is there any advice on how to deal with the errors that require Access or SQL to correct, as I have some of those to work on afterwards?

Gordon Barker
Biological Survey Data Manager
National Trust

12 Reply by sallyrankin (edited by sallyrankin 09-10-2010 05:30:34)

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

Re: Exporting locations

Gordon,

How the verification works in v6.13 onwards is explained in the Help: Search for verified and select ‘Verification of identifications’. The Verified flag on taxon and biotope occurrences wasn’t set correctly prior to v6.13 for a lot of data. In a system I looked at recently 46% of the Verified flags on taxon occurrences were wrong, in another, recently transferred from Recorder 2002, 94% were wrong. The batch update in Problems.zip will correct this by bringing the Verified flag into line with the determination type on the preferred determination. It will also correct some other problems.

Running the following query before the batch update will show you what settings are used in your database:

SELECT TOcc.Verified, TDet.Determination_Type_Key, DT.Short_Name,
Count(TOcc.Taxon_Occurrence_Key) AS CountOfTOcc_Key
FROM Taxon_Occurrence TOcc INNER JOIN Taxon_Determination TDet ON TOcc.Taxon_Occurrence_Key = TDet.Taxon_Occurrence_Key and TDet.Preferred = 1
INNER JOIN Determination_Type DT ON TDet.Determination_Type_Key = DT.Determination_Type_Key
GROUP BY TOcc.Verified, TDet.Determination_Type_Key, DT.Short_Name

As you saw, I have uploaded a batch update that will enable users to correct the problem of organisations used as recorders – see the third post in http://forums.nbn.org.uk/viewtopic.php?pid=7506#p7506. I have now uploaded another one that will insert location names when they are missing to http://forums.nbn.org.uk/uploads.php . In the system I first saw this problem, it caused Recorder 6 to crash when opening surveys containing survey events for locations with missing names. The exception message was Could not convert variant of type (Null) into type (String). The batch update uses the location key as the location name so after running it users should edit the names it inserts. To use it, copy it into \User Files\Batch Updates or wherever Tools – Options – File Locations says Batch Updates are stored in your system. You will then find it under SR Batch Updates in Tools – Run Batch Updates. As with all batch updates, please backup your database before using it and check carefully afterwards that it has made the changes correctly.

The xml reports by Mike Weideli http://forums.nbn.org.uk/viewtopic.php?pid=7320#p7320 will identify whether this problem (and others) exist in your system.

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

13 Reply by sallyrankin (edited by sallyrankin 06-05-2011 07:35:10)

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

Re: Exporting locations

Further help is now available for resolving the problems identified by the xml reports in Problems.zip which was made available by Mike Weideli via http://forums.nbn.org.uk/viewtopic.php?pid=7320#p7320 . Problems V2.zip has been uploaded to be used instead – see http://forums.nbn.org.uk/uploads.php . It contains the same xml reports and batch updates as Problems.zip – these are unchanged – but it also contains additional xml files to assist with correcting problems. The Word file in Problems V2.zip provides more information on correcting problems than its predecessor in Problems.zip.

If your Recorder system behaves unexpectedly, it could be due to problems that can be detected and resolved using Problems V2.zip. This is more likely if it contains data entered into versions of Recorder pre v6.13. If you need help with resolving any problems please consult a Recorder reseller for advice – see http://jncc.defra.gov.uk/page-4611

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