Strangely enough I am working on a very similar problem. I'll send the Batch Update when I've finished polishing it off (it's mainly the work of Mike W - I'm just tweaking, honing and documenting it.)

The difference between what I am doing and what you're doing is that I am adding a new determination for all records of species named in a CSV. It wouldn't be too much of a stretch to modify what I've got to work on a list of taxon_occurrence_keys (I presume you exported the taxon_occurrence_keys for each record into your spreadsheet?) instead of a list of species names.

I'd be really interested in seeing that batch update too Charles

Regards

I've just tested the second one on a largish dataset (~60k records) and it worked just fine, taking about 5 minutes to process. It wasn't a thorough test, though.

As a variation on Charles’ offering, Mike Weideli has written 2 xml reports and a batch update to enable users to update Recorder 6 with verification information and I have documented the process.

In summary the procedure is:
•    Run LcValidation2Generate.xml to generate an Excel file containing the species observations to be verified – this lists observations for a taxonomic group, a survey or a taxonomic group within a survey
•    Send it to the expert and ask them to append their information on verification
•    When the Excel file is returned, run LcValidation1Check.xml to check that the verification information appended to the file is valid, i.e. that it can be used to update the Recorder 6 database
•    Run LCValidation1.xml, the xml batch update, to update the Recorder 6 database with the verification information from the Excel file. This will add determinations to observations containing information from the expert, and make them the preferred determination.

I have uploaded these and the documentation to http://forums.nbn.org.uk/uploaded/LCValidation.zip. If you want to make use of them you will need to study the documentation and check that they do what you want. If not, you will need to modify them, or get one of us to modify them, which we would be happy to do for a modest fee. You will also need to test them. They come with the warning: The complexity of the Recorder 6 database enables the system to handle an extensive array of biological data for observations. This means that testing xml reports and batch updates for every possibility would be prohibitively time consuming. Thus users must test batch updates in particular to ensure that the changes made are as required.

It would be relatively easy to add additional fields to the report if these would be helpful to the expert. TOccs with Entry Date GT specified date V3.xml is included in the zip file to illustrate how to add some extra things. The report on observations to be verified might also be made slightly more sophisticated by ignoring records which have already been determined by the expert. This could be done either by specifying the expert or by ignoring anything where the Determiner is County Recorder or Specialist. It probably isn’t a good idea just to ignore records which are already marked as ‘Correct’ or ‘Considered correct’ as they may have come from sources which are not trusted. However, you could apply all sorts of rules here if it was considered appropriate. For example ignoring records marked ‘Correct’ where the LRC is the custodian, but reporting on others. 

With this version of the Batch Update the Determination_Type must always be entered, but to make life easier it could be modified to accept, say, ‘c’ as Determination_Type then translate this into ‘Correct’ when the batch update is run. As it stands records which have not been determined need to be deleted manually from the Excel file, but the batch update could easily be changed to ignore records with blank determination types.

On a related note, I'm sure I've used batch updates that work like a transaction: they run, allow you to inspect the result, then allow you to apply the result or discard and roll-back. Trouble is, I can remember where I saw this... was I imagining it?

Having said that I can't find a syntax that works for the error trapping. I think this may depend on the version of SQLServer being used. I tried a TRY CATCH block checking for the value of XACT_STATE() in the catch (which is what I would do for SQL 2005), but it didn't like the TRY (we are using SQLserver  2000 I think) and I also tried IF @@ERROR <> 0 ROLLBACK TRANSACTION and it didn't like that either.

I can at least confirm that enclosing the section between OPEN CSV1_Cursor and DEALLOCATE CSV1_Cursor within BEGIN TRANSACTION and COMMIT TRANSACTION did not interfere with the functioning of the update.

Can anyone suggest how to trap errors and rollback?

Hi All,

Just referring back to Charles' earlier point about being able to review the output of a batch update before applying the changes. You are right - this facility does exist if the batch update is configured in the correct way. Here is the relevant extract from the technical documentation:

Best Wishes

Thanks John, very useful info.

As mentioned in the docs, there is a transaction involved but what I'm not clear on is whether this is handled automatically by Recorder, of if the transaction needs to be written into the SQL. If the transaction is handled by Recorder, do all batch updates get wrapped in a transaction, even if we don't trigger a pre-commit review stage as described above?

Is there any chance we can have the latest technical docs added to the website somewhere? The section on external filters sounds like it is worth a read too.

Thanks John, that makes much more sense now (and thanks for pointing out the technical docs, I admit I hadn't taken the time to explore every corner of the new Rec6 website yet).

With respect to the batch update scripts written by Mike and referred to by Sally (posting #8) I have found that the comment field is being truncated to 100 characters which is causing me quite some inconvenience.

I verified that the R6 determination comment field is capable of holding more than 100 characters by editing one manually. Is this something that can be easily fixed?

Rich

Okay not to worry - I have a workaround (I'm applying the same comment to multiple records, so I can just put a marker in and use SQL Server to replace the marker in the taxon_determination table with the full comment).

Which element of the architechture is responsible for the 100 character limit? Is this a Recorder limitation or a limitation of an API it uses?

Rich

Sorry to confuse things even more, but what happens when the expert marks a record as "considered incorrect", and enters the "correct name"?

The way I see it it will add a determination of the "correct name" but the determination type will be set to "considered incorrect"?