1

Topic: Recorder 6 - Dictionary Upgrade 3C to 3E

This upgrade is now available. For details please see  http://forums.nbn.org.uk/viewtopic.php?pid=25356#p25356

Mike Weideli
R6 Consortium

2

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

Hi Mike, Thanks for all of your continued superb help and support with all things recorder!

My computer crashed and restarted in the midst of performing the upgrade and Recorder now shows as being at dictionary version 3D. Is there a way to run just part of the upgrade to finish off where it obviously got stopped during the crash? I've tried running again but it comes across the error message relating to 'not being able to perform DELETE clause...' etc which i assume is down to it already seeing/having part of the upgrade files present.

Many thanks,
Phil

Phillip Whelpdale
Yorkshire Wildlife Trust

3

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

Just running it again should be Ok, but the Delete Clause problems is causing issues with other users as well. I supect this will be down  to something  being out of step,  probably as a result of a dictionary issue  which could potentially have happended years ago.

I am going to look at 000003E and see if I can stop the error message. I will then provide a new version. I think R6 will run with the Dictionary up to 3D as long as the 4 index tables are rebuilt.

Mike Weideli
R6 Consortium

4

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

Please find attached a revised version of 000003E which should  run without the 'Delete' message. The problem will be specific to your machine  so I can't test it, but running it even if fails can't do any harm. If it fails again please let me know the exact error message. Copy the attched file 0000003E.sql into the scripts folder to replace the one that is there. Then run the upgrade again. the index tables will need rebuilding.

I have deleted  the revised 000003E, because I think the problem may be elsewhere. Please post again if you have an error with 0000003E.

Mike Weideli
R6 Consortium

5

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

Ah brilliant, that seems to have worked perfectly Mike! It is very posisble (if not highly likely) that our databae was out of sync in the past. We used to have our database hosted externally and accessed it via a locally installed front end which had the registry pointing to the database on the server so I remember Dan Jones having to come around all of the offices to upgrade the local installs after updating the server side.

Huge thanks for your speedy reply and help

Phillip Whelpdale
Yorkshire Wildlife Trust

6

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

Hi Mike,
Thanks for this fix, i will let you know once i have applied it.
I have the exact same problem with the dictionary upgrade, from the error message caused by the sql in '0000003E.sql', it looks as if the FK constraint 'FK_TAXON_VERSION_TAXON' is doing its job.

Just a few thoughts...
So the fix is to not delete the entries in TAXON where there is a related entry in TAXON_VERSION. I can see from the database relations how deleting these entries in the TAXON table would have a detrimental effect on the data.
This problem seems to affect only some of the recorder installations, I wonder how my data has obtained TAXON_VERSION entries that others have not?
Perhaps a previous dictionary update (that i have missed?) was supposed to delete these taxon_version entries? Or perhaps more likely, only Recorder installations from a certain time ago have these TVK entries.
If our dictionary tables are out of sync, perhaps there is a need for a 'fresh update' for the whole dictionary which would put it back in sync?

Thanks again.

7

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

A while back (2011) it was important that each group of  upgrades were run separately. If two upgrades were run togther  the  the second one didn't get actioned.  I don't know of any cases where more recent upgrades have caused problems, but you can never rule out something going wrong. 

The problem can show up a long time later when  a Taxon or other table entry is deleted.  The revised 000003E does just what you suggest  by not trying to delete the Taxon if it is in use.   A complete dictionary replacement for a live system, would be a very complicated task, because of the referencial issues and not one I think we could automate. We did have dictionary validator tool, but maintaining this was too expensive for the limited use being made of it,  so there is now a free service available to fix dictionary issues. It does involve supplying a copy of your database.  You then  get back a special dictionary update, which brings your dictionary into line with the current master. There is no need to stop using Recorder while this is done and usually the fix is available in a few days.

Mike Weideli
R6 Consortium

8

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

OK Great, this sounds like the thing we need as i have tried to apply the dictionary updates on a number of occasions and it stalls indefinitely.
Do you have the details of whom i need to contact regarding the free service to fix dictionary issues?
Thanks
Rob

9

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

See here Rob - http://forums.nbn.org.uk/viewtopic.php?id=6543

Purgle

Purgle Linham
Wiltshire & Swindon Biological Records Centre

10

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

Rob,

This update did seem to take a very long time to complete for me and it did look like it wasn't doing anything. Slight caveat in that I was only at version 3A for some reason and had to get 3B put in as well for it to happen

Gordon Barker
Biological Survey Data Manager
National Trust

11

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

With  upgrade 3C to 3E there are more than usual lines in the sql table. The sql moves TLI keys which have been deleted to alternatives and does the deletion of unwanted lines in the tables.  Once the running of this process starts there is no feedback available for the program to display. We do have a progress indicator on the processing of the changes held in the MS Access tables, but I did notice that this fills a long before completion, which is something we can look at.

Mike Weideli
R6 Consortium

12

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

Mike,

I am having the same problem with the 3C to 3E dictionary upgrade.

exception message : Error occurred running script 0000003E.sql. The error is described as: The DELETE statement conflicted with the REFERENCE constraint "FK_TAXON_VERSION_TAXON". The conflict occurred in database "NBNData", table "dbo.TAXON_VERSION", column 'TAXON_KEY'.

It looks like you posted a revised version of 000003E here a while ago but have now removed it. Could you put it back up please?

Thanks,

Phil

13

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

Hi

Unfortunately, I can't find the revised 0000003E. I will do it again asap.

Mike

Mike Weideli
R6 Consortium

14

Re: Recorder 6 - Dictionary Upgrade 3C to 3E

Hi

I attached a revised script  0000003E. Please make sure you have a backup before applying this script. If the original script didn't work then this suggests some problem with your dictionary - possibly going back a long time. Fix for this is still free until end of March. I would require a  copy of your database (a backup will do) and there is no need for you to stop using Recorder. What you get back is a special dictionary upgrade to bring your dictionary into line. After end of  March there will be a small charge for this service.

Post's attachments

0000003E.sql 422.58 kb, 2 downloads since 2018-03-01 

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