No issues with upgrade and new Addins.

I appreciate the concern regarding Index_taxon_Group, hence my warning. I will write something explaining what has happened with it. If you have used Index Taxon Group anywhere then don't install this upgrade until you have looked at the effect.  It would be helpful to know what you have used it for.

Agreed BugReport.txt shouldn't be there will not do any harm.

Oh well, I've reverted back to v6.21.1.270 and restored my pre-upgrade NBNData. I have a custom-written piece of SQL to export the NMRS database in the NBN Exchange format and it fails to write any records under the new version. So upgrading for me was a show-stopper.

Here's the code for info:

SELECT     TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY AS RecordKey, 
                      TAXON_LIST_ITEM.TAXON_VERSION_KEY AS TaxonVersionKey, 
                      SURVEY_EVENT.SURVEY_KEY AS SurveyKey, 
                      SAMPLE.SAMPLE_KEY AS SampleKey, 
                      dbo.nbn_exchange_date_to_string(SAMPLE.VAGUE_DATE_START) AS StartDate,
                      dbo.nbn_exchange_date_to_string(SAMPLE.VAGUE_DATE_END)AS EndDate, 
                      SAMPLE.VAGUE_DATE_TYPE AS DateType, 
                      SAMPLE.SPATIAL_REF AS GridReference, 
                      SAMPLE.SPATIAL_REF_SYSTEM AS Projection, 
                      CASE SAMPLE.SPATIAL_REF_SYSTEM WHEN 'OSGB' THEN CASE LEN(SAMPLE.SPATIAL_REF) 
                           WHEN 4 THEN 10000 WHEN 5 THEN 2000 WHEN 6 THEN 1000 WHEN 8 THEN 100 WHEN 10 THEN 10 ELSE 1 END WHEN 'OSNI' THEN CASE LEN(SAMPLE.SPATIAL_REF)
                           WHEN 3 THEN 10000 WHEN 4 THEN 2000 WHEN 5 THEN 1000 WHEN 7 THEN 100 WHEN 9 THEN 10 ELSE 1 END END AS [Precision], 
                      CASE CONFIDENTIAL WHEN 1 THEN 'T' ELSE 'F' END AS Sensitive, 
                      CASE ZERO_ABUNDANCE WHEN 1 THEN 'T' ELSE 'F' END AS ZeroAbundance, 
                      ISNULL(SAMPLE.LOCATION_KEY,'') AS SiteKey, 
                      SUBSTRING(ISNULL(SAMPLE.LOCATION_NAME, '') + (CASE SAMPLE.LOCATION_NAME WHEN NULL THEN '' ELSE ', ' END) 
                            + ISNULL(LOCATION_NAME.ITEM_NAME, ''),1,80) AS SiteName, 
                      SAMPLE_TYPE.SHORT_NAME AS SampleMethod, 
                      dbo.FormatEventRecorders(SAMPLE.SAMPLE_KEY) as Recorder, 
                      dbo.ufn_GetFormattedName(TAXON_DETERMINATION.DETERMINER) as Determiner,
                      ISNULL(dbo.LCFormatAbundanceData(TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY),'') as Abundance, 
                      ISNULL(SUBSTRING(dbo.ufn_RtfToPlaintext(CAST(TAXON_OCCURRENCE.COMMENT AS VARCHAR)),1,255), '') AS Comment
FROM         TAXON_OCCURRENCE INNER JOIN
                      TAXON_DETERMINATION ON TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY INNER JOIN
                      TAXON_LIST_ITEM ON TAXON_DETERMINATION.TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY INNER JOIN
                      SAMPLE ON TAXON_OCCURRENCE.SAMPLE_KEY = SAMPLE.SAMPLE_KEY INNER JOIN
                      SURVEY_EVENT ON SAMPLE.SURVEY_EVENT_KEY = SURVEY_EVENT.SURVEY_EVENT_KEY INNER JOIN
                      SAMPLE_TYPE ON SAMPLE.SAMPLE_TYPE_KEY = SAMPLE_TYPE.SAMPLE_TYPE_KEY LEFT OUTER JOIN
                      LOCATION_NAME ON SAMPLE.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY INNER JOIN
                          (SELECT     TAXON_OCCURRENCE_1.TAXON_OCCURRENCE_KEY
                            FROM          TAXON_OCCURRENCE AS TAXON_OCCURRENCE_1 INNER JOIN
                                                   TAXON_DETERMINATION AS TAXON_DETERMINATION_1 ON 
                                                   TAXON_OCCURRENCE_1.TAXON_OCCURRENCE_KEY = TAXON_DETERMINATION_1.TAXON_OCCURRENCE_KEY INNER JOIN
                                                   INDEX_TAXON_GROUP INNER JOIN
                                                   EXPORT_FILTER_TAXON ON 
                                                   INDEX_TAXON_GROUP.TAXON_LIST_ITEM_KEY = EXPORT_FILTER_TAXON.TAXON_LIST_ITEM_KEY INNER JOIN
                                                   INDEX_TAXON_SYNONYM ON 
                                                   INDEX_TAXON_GROUP.CONTAINED_LIST_ITEM_KEY = INDEX_TAXON_SYNONYM.TAXON_LIST_ITEM_KEY ON 
                                                   TAXON_DETERMINATION_1.TAXON_LIST_ITEM_KEY = INDEX_TAXON_SYNONYM.SYNONYM_LIST_ITEM_KEY
                            WHERE      (EXPORT_FILTER_TAXON.EXPORT_FILTER_KEY = 'JNCCDEV100000001') AND (TAXON_DETERMINATION_1.PREFERRED = 1) AND 
                                                   (TAXON_OCCURRENCE_1.VERIFIED <> 1) AND (TAXON_OCCURRENCE_1.CHECKED = 1)
                            GROUP BY TAXON_OCCURRENCE_1.TAXON_OCCURRENCE_KEY) AS TOK ON 
                      TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = TOK.TAXON_OCCURRENCE_KEY
WHERE     (TAXON_DETERMINATION.PREFERRED = 1) AND (TAXON_OCCURRENCE.VERIFIED <> 1) AND (TAXON_OCCURRENCE.CHECKED = 1) AND 
                      (SAMPLE.SPATIAL_REF_SYSTEM = 'OSGB' OR
                      SAMPLE.SPATIAL_REF_SYSTEM = 'OSNI') AND (LOCATION_NAME.PREFERRED IS NULL OR
                      LOCATION_NAME.PREFERRED = 1)

It looks like the link on the Dictionary Downloads page for 0000001X,IY,1Z,20,21,22,23.zip is actually pointing to http://jncc.defra.gov.uk/Files/Rec6Dict … nzipMe.zip

Hi Les

Could you try this please. It is difficult working trhough a query when you don't have the data, but i think you are using Index_taxon_group  in conjunction with Index_taxon_synonym to find all possible related Taxon_list_Item_keys. This query should do the same thing.  It will run on version 6.21 so you can try it out. It might include more/different records and if so if you can give me an indication of what is wrong  I will have another go.
                     
SELECT     TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY AS RecordKey,
                      TAXON_LIST_ITEM.TAXON_VERSION_KEY AS TaxonVersionKey,
                      SURVEY_EVENT.SURVEY_KEY AS SurveyKey,
                      SAMPLE.SAMPLE_KEY AS SampleKey,
                      dbo.nbn_exchange_date_to_string(SAMPLE.VAGUE_DATE_START) AS StartDate,
                      dbo.nbn_exchange_date_to_string(SAMPLE.VAGUE_DATE_END)AS EndDate,
                      SAMPLE.VAGUE_DATE_TYPE AS DateType,
                      SAMPLE.SPATIAL_REF AS GridReference,
                      SAMPLE.SPATIAL_REF_SYSTEM AS Projection,
                      CASE SAMPLE.SPATIAL_REF_SYSTEM WHEN 'OSGB' THEN CASE LEN(SAMPLE.SPATIAL_REF)
                           WHEN 4 THEN 10000 WHEN 5 THEN 2000 WHEN 6 THEN 1000 WHEN 8 THEN 100 WHEN 10 THEN 10 ELSE 1 END WHEN 'OSNI' THEN CASE LEN(SAMPLE.SPATIAL_REF)
                           WHEN 3 THEN 10000 WHEN 4 THEN 2000 WHEN 5 THEN 1000 WHEN 7 THEN 100 WHEN 9 THEN 10 ELSE 1 END END AS [Precision],
                      CASE CONFIDENTIAL WHEN 1 THEN 'T' ELSE 'F' END AS Sensitive,
                      CASE ZERO_ABUNDANCE WHEN 1 THEN 'T' ELSE 'F' END AS ZeroAbundance,
                      ISNULL(SAMPLE.LOCATION_KEY,'') AS SiteKey,
                      SUBSTRING(ISNULL(SAMPLE.LOCATION_NAME, '') + (CASE SAMPLE.LOCATION_NAME WHEN NULL THEN '' ELSE ', ' END)
                            + ISNULL(LOCATION_NAME.ITEM_NAME, ''),1,80) AS SiteName,
                      SAMPLE_TYPE.SHORT_NAME AS SampleMethod,
                      dbo.FormatEventRecorders(SAMPLE.SAMPLE_KEY) as Recorder,
                      dbo.ufn_GetFormattedName(TAXON_DETERMINATION.DETERMINER) as Determiner,
                      ISNULL(dbo.LCFormatAbundanceData(TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY),'') as Abundance,
                      ISNULL(SUBSTRING(dbo.ufn_RtfToPlaintext(CAST(TAXON_OCCURRENCE.COMMENT AS VARCHAR)),1,255), '') AS Comment
FROM         TAXON_OCCURRENCE INNER JOIN
                      TAXON_DETERMINATION ON TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = TAXON_DETERMINATION.TAXON_OCCURRENCE_KEY INNER JOIN
                      TAXON_LIST_ITEM ON TAXON_DETERMINATION.TAXON_LIST_ITEM_KEY = TAXON_LIST_ITEM.TAXON_LIST_ITEM_KEY INNER JOIN
                      SAMPLE ON TAXON_OCCURRENCE.SAMPLE_KEY = SAMPLE.SAMPLE_KEY INNER JOIN
                      SURVEY_EVENT ON SAMPLE.SURVEY_EVENT_KEY = SURVEY_EVENT.SURVEY_EVENT_KEY INNER JOIN
                      SAMPLE_TYPE ON SAMPLE.SAMPLE_TYPE_KEY = SAMPLE_TYPE.SAMPLE_TYPE_KEY LEFT OUTER JOIN
                      LOCATION_NAME ON SAMPLE.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY INNER JOIN
                          (SELECT     TAXON_OCCURRENCE_1.TAXON_OCCURRENCE_KEY
                            FROM          TAXON_OCCURRENCE AS TAXON_OCCURRENCE_1 INNER JOIN
                                                   TAXON_DETERMINATION AS TAXON_DETERMINATION_1 ON
                                                   TAXON_OCCURRENCE_1.TAXON_OCCURRENCE_KEY = TAXON_DETERMINATION_1.TAXON_OCCURRENCE_KEY INNER JOIN
                                                   INDEX_TAXON_NAME INNER JOIN
                                                   EXPORT_FILTER_TAXON ON
                                                   INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY = EXPORT_FILTER_TAXON.TAXON_LIST_ITEM_KEY INNER JOIN
                                                   INDEX_TAXON_NAME ITN2
                                                   ON ITN2.RECOMMENDED_TAXON_LIST_ITEM_KEY = INDEX_TAXON_NAME.RECOMMENDED_TAXON_LIST_ITEM_KEY
                                                   ON TAXON_DETERMINATION_1.TAXON_LIST_ITEM_KEY = ITN2.TAXON_LIST_ITEM_KEY
                            WHERE      (EXPORT_FILTER_TAXON.EXPORT_FILTER_KEY = 'JNCCDEV100000001') AND (TAXON_DETERMINATION_1.PREFERRED = 1) AND
                                                   (TAXON_OCCURRENCE_1.VERIFIED <> 1) AND (TAXON_OCCURRENCE_1.CHECKED = 1)
                            GROUP BY TAXON_OCCURRENCE_1.TAXON_OCCURRENCE_KEY) AS TOK ON
                      TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY = TOK.TAXON_OCCURRENCE_KEY
WHERE     (TAXON_DETERMINATION.PREFERRED = 1) AND (TAXON_OCCURRENCE.VERIFIED <> 1) AND (TAXON_OCCURRENCE.CHECKED = 1) AND
                      (SAMPLE.SPATIAL_REF_SYSTEM = 'OSGB' OR
                      SAMPLE.SPATIAL_REF_SYSTEM = 'OSNI') AND (LOCATION_NAME.PREFERRED IS NULL OR
                      LOCATION_NAME.PREFERRED = 1)

As a matter of onterest, and I should probably already know the answer, how did you revert back to the previous version? Just in case I need to..

I had a feeling you were going to say that.
We have pretty much automated network backups so it should be less of a performance, but I would still prefer to avoid having to do it.

Hi Mike,

Emailed some files to you directly. The documentation should help answer these and other questions you may have in addition.

Should be corrected by JNCC overnight.

I have just bitten the bullet and done the upgrade. No sign of any problems yet, but we will see.

I urgently need to some feedback on this issue now please. We have staff waiting to import large datasets and I don't yet know whether there is a possibility of a quick fix for the Comments field problem, or if I am going to have to rollback the upgrade.

Thi list han't been included before as it repeats information in preferred lists. It has many thousands of entries which take up a lot of space. In the last upgrade there were  five entries in the Organism table which only appeared on this list, and because an incomplete Organism table can cause problems in many areas, the intention was to just include the Forestry Commission taxa which were needed. This doesn't seem to have worked as intended, leaving, as you put, 'as mess'. I will tidy it up at the next opportunity. I suggest you don't use it unless absolutely necessary, but all the entries there will remain, just with a better structure, so if it is used then this will not cause a problem.