1 Topic by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 262

Topic: Extracting changed records

We have been extracting records changed since a particular date, for reports and to export to another database, by using the data last changed field on the sample or survey event. Unfortunately many of our changes were to the location hierarchy, particularly to rationalize multiple locations that were actually the same site by merging them. The attached samples and survey events were correctly updated to point to the merged locations, but the date last changed field was not updated so the subsequent export can't pick them up.

It's not a show stopper, but a time-waster as for us it means that the whole survey has to be deleted in the receiving system and then imported again to pick up the changes. This time round we have 148 affected surveys....

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

2 Reply by MikeWeideli

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

Re: Extracting changed records

Janet

Probably a bit late, but if you run the following script in SQL Management Tools this will deal with merged locations by changing the sample changed date and setting changed by to the default user.    It has a slight  side effect which is that if you changes the location of a sample through the observation hierarchy it will do just the same which will mean that the person making the change will not be correctly recorded.

USE [NBNData]
GO
/****** Object:  Trigger [dbo].[SAMPLELocationChange]    Script Date: 12/12/2013 21:47:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[SAMPLELocationChange] ON [dbo].[SAMPLE] AFTER UPDATE AS IF UPDATE(Location_Key)  UPDATE SAMPLE SET SAMPLE.Changed_Date = getdate(),  SAMPLE.Changed_By =  'TESTDATA00000001'  FROM SAMPLE INNER JOIN INSERTED ON SAMPLE.SAMPLE_KEY = INSERTED.SAMPLE_KEY

Mike Weideli

MikeWeideli's Website

3 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 262

Re: Extracting changed records

Many thanks for this. I've tested it quickly and it seems to work fine. Do we need something similar for the survey events, to keep them in line?

Also, and more seriously, I've just spotted a similar issue with changes to the spatial reference triggered by changing the spatial ref on the location. They don't seem to be updating the sample changed date either, and so won't be picked up by the export. Can this be done?

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

4 Reply by MikeWeideli

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

Re: Extracting changed records

The following seems to work. I have updated the original one to only make the change if it hasn't already been done, which should limit it to merged items. The second one will pick up any change to the Location and bring the Sample in line.  As it stands changing the name or making another name the preferred  name will not have any effect. The problem with this is knowing where to draw the line.

USE [NBNData]
GO
/****** Object:  Trigger [dbo].[SAMPLELocationChange]    Script Date: 13/12/2013 13:10:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[SAMPLELocationChange] ON [dbo].[SAMPLE] AFTER UPDATE AS IF UPDATE(Location_Key) AND NOT UPDATE (Changed_date) UPDATE
SAMPLE SET SAMPLE.Changed_Date = getdate(),  SAMPLE.Changed_By =  SAMPLE.Entered_By
FROM SAMPLE INNER JOIN INSERTED ON SAMPLE.SAMPLE_KEY = INSERTED.SAMPLE_KEY

GO
/****** Object:  Trigger [dbo].[LocationChange]    Script Date: 13/12/2013 13:10:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[LocationChange] ON [dbo].[Location] AFTER UPDATE AS IF UPDATE(Changed_date) UPDATE
SAMPLE SET SAMPLE.Changed_Date = INSERTED.CHANGED_DATE,  SAMPLE.Changed_By =  INSERTED.Changed_By
FROM SAMPLE INNER JOIN INSERTED ON SAMPLE.LOCATION_KEY = INSERTED.LOCATION_KEY

Mike Weideli

MikeWeideli's Website

5 Reply by Janet Simkin (edited by Janet Simkin 13-12-2013 15:46:08)

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 262

Re: Extracting changed records

Nearly! This now updates the sample last changed information for changes to the location grid ref and for merges, which is what we need.

It also updates it for a change to the location name, which makes sense, but unfortunately Recorder now crashes most times the location name is needed. This happens whether the change is made by editting the existing preferred name (sometimes, not always) or by selecting a different preferred name (so far always). I suspect that it is falling over if there is more than one sample affected by the change but this needs more testing.


date/time         : 2013-12-13, 14:49:25, 660ms
computer name     : JANET-PC
user name         : janet <admin>
registered owner  : janet
operating system  : Windows 7 x64 Service Pack 1 build 7601
system language   : English
system up time    : 4 hours 53 minutes
program up time   : 4 minutes 27 seconds
processors        : 4x Intel(R) Core(TM) i5-2410M CPU @ 2.30GHz
physical memory   : 881/4003 MB (free/total)
free disk space   : (C:) 232.42 GB
display mode      : 1366x768, 32 bit
process id        : $27b8
allocated memory  : 47.23 MB
command line      : RecorderApp.exe /1442988
executable        : RecorderApp.exe
exec. date/time   : 2013-07-11 16:25
version           : 6.19.4.267
compiled with     : Delphi 7
madExcept version : 3.0n
callstack crc     : $8fb0f51c, $364ab4ff, $364ab4ff
exception number  : 1
exception class   : EDatabaseError
exception message : Key column information is insufficient or incorrect. Too many rows were affected by update.

main thread ($1c44):
0052d8e4 RecorderApp.exe DB                      DatabaseError
0059f916 RecorderApp.exe ADODB                   TCustomADODataSet.InternalPost
77ac012e ntdll.dll                               KiUserExceptionDispatcher
00404953 RecorderApp.exe System                  @AfterConstruction
771bc408 KERNELBASE.dll                          RaiseException
00404953 RecorderApp.exe System                  @AfterConstruction
00407895 RecorderApp.exe System                  @CheckAutoResult
0059f5b0 RecorderApp.exe ADODB                   UpdateData
0059f807 RecorderApp.exe ADODB                   TCustomADODataSet.InternalPost
0053d6a5 RecorderApp.exe DB                      TDataSet.CheckOperation
0053d1db RecorderApp.exe DB                      TDataSet.Post
0078f9af RecorderApp.exe LocationDetails 959 +84 TfrmLocationDetails.bbSaveClick
004dd2c8 RecorderApp.exe Controls                TControl.Click
004d23d0 RecorderApp.exe StdCtrls                TButton.Click
004d24c4 RecorderApp.exe StdCtrls                TButton.CNCommand
004dd130 RecorderApp.exe Controls                TControl.WndProc
004e018f RecorderApp.exe Controls                TWinControl.WndProc
004d2294 RecorderApp.exe StdCtrls                TButtonControl.WndProc
004dcf00 RecorderApp.exe Controls                TControl.Perform
004e02c7 RecorderApp.exe Controls                DoControlMsg
004e091f RecorderApp.exe Controls                TWinControl.WMCommand
004dd130 RecorderApp.exe Controls                TControl.WndProc
004e018f RecorderApp.exe Controls                TWinControl.WndProc
00430c3c RecorderApp.exe Classes                 StdWndProc
004dfe0c RecorderApp.exe Controls                TWinControl.MainWndProc
00430c3c RecorderApp.exe Classes                 StdWndProc
759f96c0 user32.dll                              SendMessageW
75a07945 user32.dll                              CallWindowProcA
004e0273 RecorderApp.exe Controls                TWinControl.DefaultHandler
004dd6d0 RecorderApp.exe Controls                TControl.WMLButtonUp
004dd130 RecorderApp.exe Controls                TControl.WndProc
004e018f RecorderApp.exe Controls                TWinControl.WndProc
004d2294 RecorderApp.exe StdCtrls                TButtonControl.WndProc
004dfe0c RecorderApp.exe Controls                TWinControl.MainWndProc
00430c3c RecorderApp.exe Classes                 StdWndProc
759f7bc5 user32.dll                              DispatchMessageA
004fe2fb RecorderApp.exe Forms                   TApplication.ProcessMessage
004fe332 RecorderApp.exe Forms                   TApplication.HandleMessage
004fe562 RecorderApp.exe Forms                   TApplication.Run
00a91ee8 RecorderApp.exe RecorderApp     414 +79 initialization
77593368 kernel32.dll                            BaseThreadInitThunk

thread $1a2c:
77ad0156 ntdll.dll                 NtWaitForMultipleObjects
771c15e3 KERNELBASE.dll            WaitForMultipleObjectsEx
775919f7 kernel32.dll              WaitForMultipleObjectsEx
75a00864 user32.dll                MsgWaitForMultipleObjectsEx
75a00b64 user32.dll                MsgWaitForMultipleObjects
00476bed RecorderApp.exe madExcept CallThreadProcSafe
00476c57 RecorderApp.exe madExcept ThreadExceptFrame
77593368 kernel32.dll              BaseThreadInitThunk
>> created by main thread ($1c44) at:
77593490 kernel32.dll              CreateThread

thread $1ec4:
77ad0156 ntdll.dll     NtWaitForMultipleObjects
77593368 kernel32.dll  BaseThreadInitThunk

thread $2794:
77ad1f3f ntdll.dll     NtWaitForWorkViaWorkerFactory
77593368 kernel32.dll  BaseThreadInitThunk

thread $2664:
77acf8ca ntdll.dll                 NtWaitForSingleObject
771c1497 KERNELBASE.dll            WaitForSingleObjectEx
7759118f kernel32.dll              WaitForSingleObjectEx
77591143 kernel32.dll              WaitForSingleObject
00476bed RecorderApp.exe madExcept CallThreadProcSafe
00476c57 RecorderApp.exe madExcept ThreadExceptFrame
77593368 kernel32.dll              BaseThreadInitThunk
>> created by main thread ($1c44) at:
77593490 kernel32.dll              CreateThread

thread $22e0:
759f7c18 user32.dll                GetMessageA
77001323 msvcrt.dll                _endthreadex
00476bed RecorderApp.exe madExcept CallThreadProcSafe
00476c57 RecorderApp.exe madExcept ThreadExceptFrame
77593368 kernel32.dll              BaseThreadInitThunk
>> created by main thread ($1c44) at:
77593490 kernel32.dll              CreateThread

modules:
00240000 C4DLL.DLL            1.0.0.1            C:\Recorder 6
002b0000 MS5User.Dll          5.0.0.4            C:\Recorder 6
002e0000 ms5cnv.dll           5.0.0.10           C:\Recorder 6
00400000 RecorderApp.exe      6.19.4.267         C:\Recorder 6
05850000 GOTOGR~1.OCX         2.0.0.16           C:\RECORD~1\Addins
05a70000 XMLSYS~1.DLL         1.0.4.16           C:\RECORD~1\Addins
065e0000 BulkLoad.ocx         6.0.0.10           C:\RECORD~1\Addins
06b10000 GeoDLL32.DLL         9.2.5.0            C:\Recorder 6
07970000 GoToKey.ocx          6.0.1.10           C:\RECORD~1\Addins
07b90000 LASTKE~1.OCX         6.0.0.6            C:\RECORD~1\Addins
07d90000 SHOWUN~1.OCX         6.0.0.13           C:\RECORD~1\Addins
07fa0000 STDVAL~1.DLL         6.15.1.230         C:\RECORD~1\Addins
08180000 SURVEY~1.OCX         6.0.0.25           C:\RECORD~1\Addins
084a0000 VO27RUN.DLL          2.7.2.2735         C:\Recorder 6
10000000 MS5.Dll              5.0.0.12           C:\Recorder 6
63c60000 msadce.dll           6.1.7601.17514     C:\Program Files (x86)\Common Files\System\msadc
63d80000 sqloledb.dll         6.1.7601.17514     C:\Program Files (x86)\Common Files\System\Ole DB
63eb0000 comsvcs.dll          2001.12.8530.16385 C:\Windows\system32
63ff0000 oledb32.dll          6.1.7601.17514     C:\Program Files (x86)\Common Files\System\Ole DB
65520000 msscript.ocx         1.0.7600.16385     C:\Windows\SysWOW64
65560000 msado15.dll          6.1.7601.17857     C:\Program Files (x86)\Common Files\System\ado
67d80000 HHCtrl.ocx           6.1.7600.16385     C:\Windows\system32
67e70000 DBNETLIB.DLL         6.1.7600.16385     C:\Windows\system32
67ea0000 MSDATL3.dll          6.1.7600.16385     C:\Program Files (x86)\Common Files\System\Ole DB
682d0000 msadcer.dll          6.1.7600.16385     C:\Program Files (x86)\Common Files\System\msadc
682e0000 OLEDB32R.DLL         6.1.7600.16385     C:\Program Files (x86)\Common Files\System\Ole DB
68450000 DBnmpNTw.dll         6.1.7600.16385     C:\Windows\system32
688c0000 MSDART.DLL           6.1.7600.16385     C:\Windows\system32
68a80000 ATL.DLL              3.5.2284.0         C:\Windows\system32
69b10000 msxml4.dll           4.20.9876.0        c:\Windows\SysWOW64
6a3d0000 SQLOLEDB.RLL         6.1.7600.16385     C:\Program Files (x86)\Common Files\System\Ole DB
6b740000 wsock32.dll          6.1.7600.16385     C:\Windows\system32
6b750000 SHFolder.dll         6.1.7600.16385     C:\Windows\system32
6df50000 jscript.dll          5.8.9600.16428     C:\Windows\SysWow64
6e060000 olepro32.dll         6.1.7601.17514     C:\Windows\system32
6e080000 RICHED20.dll         5.31.23.1230       C:\Windows\system32
6e1a0000 odbc32.dll           6.1.7601.17514     C:\Windows\system32
71430000 RICHED32.DLL         6.1.7601.17514     C:\Windows\system32
71440000 odbcint.dll          6.1.7600.16385     C:\Windows\system32
717b0000 ntdsapi.dll          6.1.7600.16385     C:\Windows\system32
71870000 bcryptprimitives.dll 6.1.7600.16385     C:\Windows\SysWOW64
71cf0000 bcrypt.dll           6.1.7600.16385     C:\Windows\system32
71f30000 ncrypt.dll           6.1.7601.18270     C:\Windows\system32
720f0000 dwmapi.dll           6.1.7600.16385     C:\Windows\system32
72110000 saHook.dll           3.6.3.549          c:\PROGRA~2\mcafee\SITEAD~1
72310000 mpr.dll              6.1.7600.16385     C:\Windows\system32
72330000 winspool.drv         6.1.7601.17514     C:\Windows\system32
72820000 SXS.DLL              6.1.7601.17514     C:\Windows\system32
72940000 version.dll          6.1.7600.16385     C:\Windows\system32
72b70000 uxtheme.dll          6.1.7600.16385     C:\Windows\system32
72c10000 security.dll         6.1.7600.16385     C:\Windows\system32
72c20000 schannel.dll         6.1.7601.18270     C:\Windows\SysWOW64
72cf0000 cryptdll.dll         6.1.7600.16385     C:\Windows\system32
72d10000 msv1_0.DLL           6.1.7601.17514     C:\Windows\SysWOW64
72e40000 winmm.dll            6.1.7601.17514     C:\Windows\system32
72f50000 credssp.dll          6.1.7601.17514     C:\Windows\system32
72ff0000 wkscli.dll           6.1.7601.17514     C:\Windows\system32
73000000 srvcli.dll           6.1.7601.17514     C:\Windows\system32
73020000 netutils.dll         6.1.7601.17514     C:\Windows\system32
73030000 NETAPI32.dll         6.1.7601.17887     C:\Windows\system32
73060000 SECUR32.DLL          6.1.7601.18270     C:\Windows\system32
73090000 RpcRtRemote.dll      6.1.7601.17514     C:\Windows\system32
730a0000 rsaenh.dll           6.1.7600.16385     C:\Windows\system32
730e0000 CRYPTSP.dll          6.1.7600.16385     C:\Windows\system32
73100000 Comctl32.dll         6.10.7601.17514    C:\Windows\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.7601.17514_none_41e6975e2bd6f2b2
74e20000 gdiplus.dll          6.1.7601.18120     C:\Windows\WinSxS\x86_microsoft.windows.gdiplus_6595b64144ccf1df_1.1.7601.18120_none_72d2e82386681b36
74fb0000 comctl32.dll         5.82.7601.18201    C:\Windows\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_5.82.7601.18201_none_ec80f00e8593ece5
754c0000 CRYPTBASE.dll        6.1.7600.16385     C:\Windows\syswow64
754d0000 SspiCli.dll          6.1.7601.18270     C:\Windows\syswow64
75700000 LPK.dll              6.1.7601.18177     C:\Windows\syswow64
75710000 SHLWAPI.dll          6.1.7601.17514     C:\Windows\syswow64
75770000 comdlg32.dll         6.1.7601.17514     C:\Windows\syswow64
75800000 MSASN1.dll           6.1.7601.17514     C:\Windows\syswow64
759e0000 user32.dll           6.1.7601.17514     C:\Windows\syswow64
75ae0000 shell32.dll          6.1.7601.18222     C:\Windows\syswow64
76880000 CLBCatQ.DLL          2001.12.8530.16385 C:\Windows\syswow64
76910000 MSCTF.dll            6.1.7600.16385     C:\Windows\syswow64
769e0000 ole32.dll            6.1.7601.17514     C:\Windows\syswow64
76b40000 sechost.dll          6.1.7600.16385     C:\Windows\SysWOW64
76b60000 IMM32.DLL            6.1.7601.17514     C:\Windows\system32
76bc0000 USP10.dll            1.626.7601.18009   C:\Windows\syswow64
76e80000 WS2_32.dll           6.1.7601.17514     C:\Windows\syswow64
76ff0000 msvcrt.dll           7.0.7601.17744     C:\Windows\syswow64
770d0000 oleaut32.dll         6.1.7601.17676     C:\Windows\syswow64
771b0000 KERNELBASE.dll       6.1.7601.18229     C:\Windows\syswow64
77200000 RPCRT4.dll           6.1.7601.18205     C:\Windows\syswow64
772f0000 ADVAPI32.dll         6.1.7601.18247     C:\Windows\syswow64
773d0000 GDI32.dll            6.1.7601.18275     C:\Windows\syswow64
77460000 CRYPT32.dll          6.1.7601.18277     C:\Windows\syswow64
77580000 kernel32.dll         6.1.7601.18229     C:\Windows\syswow64
77a80000 NSI.dll              6.1.7600.16385     C:\Windows\syswow64
77ab0000 ntdll.dll            6.1.7601.18247     C:\Windows\SysWOW64

processes:
0000 Idle                           0 0   0
0004 System                         0 0   0
0160 smss.exe                       0 0   0
025c csrss.exe                      0 0   0
029c wininit.exe                    0 0   0
02b0 csrss.exe                      1 0   0
02e4 winlogon.exe                   1 0   0
0308 services.exe                   0 0   0
031c lsass.exe                      0 0   0
0324 lsm.exe                        0 0   0
0384 svchost.exe                    0 0   0
03d0 svchost.exe                    0 0   0
0178 svchost.exe                    0 0   0
0218 svchost.exe                    0 0   0
0284 svchost.exe                    0 0   0
0354 svchost.exe                    0 0   0
03e0 stacsv64.exe                   0 0   0
046c svchost.exe                    0 0   0
04d8 svchost.exe                    0 0   0
0588 wlanext.exe                    0 0   0
0590 conhost.exe                    0 0   0
05cc spoolsv.exe                    0 0   0
0600 svchost.exe                    0 0   0
06b4 PhotoshopElementsFileAgent.exe 0 0   0
06fc armsvc.exe                     0 0   0
0740 AESTSr64.exe                   0 0   0
0760 svchost.exe                    0 0   0
0788 devmonsrv.exe                  0 0   0
07c8 mDNSResponder.exe              0 0   0
07f0 MAHostService.exe              0 0   0
04c0 EvtEng.exe                     0 0   0
0708 taskhost.exe                   1 22  17  normal
081c dwm.exe                        1 21  4   high
0840 explorer.exe                   1 355 231 normal
08a4 node.exe                       0 0   0
08ac conhost.exe                    0 0   0
08d0 svchost.exe                    0 0   0
08f4 svchost.exe                    0 0   0
0910 inetinfo.exe                   0 0   0
0934 McSvHost.exe                   0 0   0
0a70 mfevtps.exe                    0 0   0
0ba4 rundll32.exe                   1 0   0
0bac sqlservr.exe                   0 0   0
0bb4 rundll32.exe                   1 0   0
0bc0 rundll32.exe                   1 0   0
05b8 pcCMService.exe                0 0   0
0974 pcCMService.exe                0 0   0
0908 rndlresolversvc.exe            0 0   0
0bf0 RegSrvc.exe                    0 0   0
0ab4 SeaPort.EXE                    0 0   0
0c30 SftService.exe                 0 0   0
0c70 sqlwriter.exe                  0 0   0
0c8c svchost.exe                    0 0   0
0cb0 svchost.exe                    0 0   0
0d1c WLIDSVC.EXE                    0 0   0
0da4 WLIDSVCM.EXE                   0 0   0
0e04 TOASTER.EXE                    1 22  19  normal       C:\Program Files (x86)\Dell DataSafe Local Backup
0e84 STSERVICE.EXE                  1 60  10  normal       C:\Program Files (x86)\Dell DataSafe Local Backup\COMPONENTS\SCHEDULER
0ebc DSUpd.exe                      1 4   2   normal       C:\Program Files (x86)\Dell DataSafe Local Backup\Components\DSUpdate
0f98 mcshield.exe                   0 0   0
0fb4 node.exe                       1 9   2   normal       C:\Program Files (x86)\BT Broadband Desktop Help\btbb\MA\8.3.1.7.bt.1.3\ma\bin
0fc8 conhost.exe                    1 29  10  normal
0fd0 mfefire.exe                    0 0   0
1034 svchost.exe                    0 0   0
1058 unsecapp.exe                   0 0   0
10f8 svchost.exe                    0 0   0
1254 WmiPrvSE.exe                   0 0   0
12e8 Apoint.exe                     1 84  40  normal
0e14 quickset.exe                   1 24  17  normal
149c SetPoint.exe                   1 37  16  normal
1510 BTHelpNotifier.exe             1 12  12  normal
152c KHALMNPR.exe                   1 9   15  normal
1624 ApMsgFwd.exe                   1 9   6   normal
1638 sttray64.exe                   1 16  16  normal
1650 igfxtray.exe                   1 12  5   normal
1668 hkcmd.exe                      1 9   18  normal
16b8 pcContextHookShim.exe          1 15  7   normal       C:\Program Files (x86)\Common Files\Motive
16d4 igfxpers.exe                   1 9   5   normal
1778 SearchIndexer.exe              0 0   0
1794 IAStorIcon.exe                 1 33  16  normal       C:\Program Files (x86)\Intel\Intel(R) Rapid Storage Technology
17a4 nusb3mon.exe                   1 18  10  normal       C:\Program Files (x86)\Renesas Electronics\USB 3.0 Host Controller Driver\Application
17b4 hidfind.exe                    1 9   4   normal
17d4 mcagent.exe                    1 36  56  normal
0874 ApntEx.exe                     1 13  7   normal
1230 conhost.exe                    1 27  11  normal
1478 concentr.exe                   1 41  23  normal       C:\Program Files (x86)\Citrix\ICA Client
15f4 wmpnetwk.exe                   0 0   0
0900 Receiver.exe                   1 48  29  normal       C:\Program Files (x86)\Citrix\ICA Client\Receiver
0114 wfcrun32.exe                   1 23  29  normal       C:\Program Files (x86)\Citrix\ICA Client
0e68 SelfServicePlugin.exe          1 22  18  normal       C:\Program Files (x86)\Citrix\SelfServicePlugin
14e4 svchost.exe                    0 0   0
0dc0 BTHSAmpPalService.exe          0 0   0
0f10 BTHSSecurityMgr.exe            0 0   0
147c IAStorDataMgrSvc.exe           0 0   0
132c LMS.exe                        0 0   0
1ccc svchost.exe                    0 0   0
1e14 svchost.exe                    0 0   0
1f98 UNS.exe                        0 0   0
1898 IELowutil.exe                  1 9   4   below normal C:\Program Files (x86)\Internet Explorer
1abc SelfService.exe                1 24  20  normal       C:\Program Files (x86)\Citrix\SelfServicePlugin
2060 winpm-32.exe                   1 260 367 normal       C:\PMail\Programs
27f4 splwow64.exe                   1 11  5   normal
2578 chrome.exe                     1 98  69  normal       C:\Program Files (x86)\Google\Chrome\Application
1ebc chrome.exe                     1 13  4   normal       C:\Program Files (x86)\Google\Chrome\Application
1a9c chrome.exe                     1 19  1   below normal C:\Program Files (x86)\Google\Chrome\Application
19c4 chrome.exe                     1 105 1   below normal C:\Program Files (x86)\Google\Chrome\Application
1d9c chrome.exe                     1 9   1   normal       C:\Program Files (x86)\Google\Chrome\Application
25bc chrome.exe                     1 10  1   normal       C:\Program Files (x86)\Google\Chrome\Application
1df0 chrome.exe                     1 10  1   normal       C:\Program Files (x86)\Google\Chrome\Application
0b70 chrome.exe                     1 10  1   normal       C:\Program Files (x86)\Google\Chrome\Application
2648 chrome.exe                     1 9   12  normal       C:\Program Files (x86)\Google\Chrome\Application
2280 audiodg.exe                    0 0   0
27b8 RecorderApp.exe                1 295 254 normal       C:\Recorder 6
2260 taskeng.exe                    0 0   0
2554 chrome.exe                     1 84  1   normal       C:\Program Files (x86)\Google\Chrome\Application
2494 saui.exe                       1 9   4   normal       c:\PROGRA~2\mcafee\SITEAD~1

Janet Simkin
British Lichen Society

Janet Simkin's Website

6 Reply by MikeWeideli

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

Re: Extracting changed records

I only tested it with a few records. Suggest you remove it while I take a look. If you open the tables in Management Studion and go to the location table you will see Triggers listed below it. Delete the one called LocationChange.

Mike Weideli

MikeWeideli's Website

7 Reply by MikeWeideli

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

Re: Extracting changed records

This is too complicated for a  Trigger as it is interacting with R6's update of changed by.  The only way I can do it for the moment is to use a Batch update (attached). It would need to be run before you do an export. It will be in the Update menu of the Batch Updates. This does deal with changes to the Location names.

Post's attachments

LCLocationChangedDate.xml 565 b, 3 downloads since 2013-12-14 

You don't have the permssions to download the attachments of this post.
Mike Weideli

MikeWeideli's Website

8 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 262

Re: Extracting changed records

Thanks, Mike, that seems to have sorted it out.

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

9 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 262

Re: Extracting changed records

Mike,

We have done some detailed testing on this now and feel as if we have spent the last week banging our heads against a brick wall. The batch update went some way towards solving our problem, but sometimes it works and sometimes it doesn't. This is what we have worked out so far:

1. Changing the grid ref of a location updates the Last Changed on that location, but the batch update doesn't pick it up.

2. Propagating the grid ref change to the attached samples does not set their Last Changed either, although a significant change has been made.

3. Merging two locations does not update the Last Changed on the location, so there is nothing for the batch update to pick up and no way for us to trace the merges.

4. Having merged there will be at least two location names, manually deleting one of them does update its Last Changed (but if the names are different we don't do that), and anyway the batch update still doesn't pick it up.

5. Moving a location to a new parent location does update the Last Changed, the batch update does pick it up and so it finds its way into the next export, but....

6. .... the updated location is rejected at the last stage of import with the message in ImportRejects.txt:

Failed to insert LC00030500000Y3I into LOCATION   
Database Error Message:
The UPDATE statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_LOCATION_LOCATION". The conflict occurred in database "NBNData", table "dbo.LOCATION", column 'LOCATION_KEY'.The statement has been terminated.

We have now had several thousand such rejects, and so have many differences between the locations in one system and the other. We have tried UpdateLastKey and running R6 as administrator, just in case.


I'm beginning to wonder whether anyone else is actually using the export and import facilities to transfer records between copies of R6, and if so whether they use locations in the way we do?

My head hurts.

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

10 Reply by MikeWeideli

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

Re: Extracting changed records

Possible the batch update problem is with comparing dates in the dd/mm/yyyy format. Which is why it works with some dates and not with others. Using  Date Diff  should solve any issues. Merging doesn't pick up the changes, but the original trigger I sent should have done this by changing the sample changed_date when the Location key in the sample changes.

My guess is that the rejections are going to be because Location parent keys are missing from the importing system.  I think there is a way of exporting all Locations and importing these into a new system. 

This needs  a bit more thought before doing anymore. Possibly an addin  could identify all  changes and generate an export file.   This would however need all merges to identify that a change had taken place. I will see what Sally and John think.

Mike Weideli

MikeWeideli's Website

11 Reply by sallyrankin

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

Re: Extracting changed records

To export multiple locations, put them in a rucksack, then use Tools - Export data... with the rucksack open. You will be given the option to exclude observations and/or sub-sites and the resulting export file will contain all the locations, people (recorders) and documents in the Rucksack lists. See Help – Index tab – Export – Rucksack.

Parent locations not being exported was a problem sometime ago but I thought it had been fixed. Perhaps not when custody is reassigned.

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

12 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 262

Re: Extracting changed records

Thanks, Sally.

Using the rucksack to export all the locations and observations for each vice county does seem to have solved the problem, so long as we remember to do each import twice. The first time there will still be rejects because some of the subsites are imported before their parents, but the second time round the parents are all there and it works.

Incidentally, we hadn't been doing the exports this way because they were failing to pick up a lot of records, but running the Data Correction batch update seems to have fixed that as well.

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

13 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Extracting changed records

We have just noticed this problem too. reading through the thread above, i am not sure I want to try any of the solutions given.

Has anyone given this any more thought?

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website