Hi Mike,

Dictionary version is 00000014

The upgrade itself seemed to have gone fine, as did the rebuilding of Group Index, it was only on running the rebuild of the name index that the problem occurred and i received the error.

The R6 report wizard returns records but with no Taxon or designation attached, though on selecting an individual record it is possible to see the species name so at least the info is still there, i am assuming the problem with the report is just a symptom of the index not being rebuilt.

Script 14 should  clear down the index tables.  Could you see in you have anything in table Index_Taxon_Name ?

Mike

Can you just delete the contents of the table. If you have management studio the query is

TRUNCATE TABLE  INDEX_TAXON_NAME

If  not change the Dict Seq in the settings table to 00000013 and run the dictionary upgrade again. 

If the rebuild of the taxon name index doesn't work after that we will need to dig a bit deeper. Perhaps Index_taxon_name is only partly being populated  and is failing at a point wherea duplicate key is being found, but I have never seen this before.

Mike

It shouldn't matter about the order apart from the fact that index_taxon_designation has to be done last. I assume that after the truncate the table is empty, but that after the rebuild it some data in it, but not the 500,000 rows which there should be ?

I attach the SQL which populates Index_Taxon_Name.  I would be grateful if you could run this in management studio to see where the process is failing.

By working through it looks like the  following may be where the problem is.  Please could you run this select statement. I don't think it will produced many rows and if it doesn't send me the results. If it is too large for this please could you have a look down and see if there is anything which stands out as a duplicate or which is different.

SELECT TLI.Taxon_List_Item_Key, TLI.Taxon_List_Version_Key,   TUN.Item_Name, CASE WHEN TR3.List_Font_Italic = 1 AND TUN.Language = 'La' THEN 1 ELSE 0 END,   T2.Item_Name, CASE WHEN TR3.List_Font_Italic = 1 AND T2.Language = 'La' THEN 1 ELSE 0 END,   T3.Item_Name, CASE WHEN TR3.List_Font_Italic = 1 AND T3.Language = 'La' THEN 1 ELSE 0 END,   0
FROM Taxon_User_Name AS TUN LEFT JOIN Taxon_List_Item AS TLI ON TLI.Taxon_List_Item_Key = TUN.Taxon_List_Item_Key LEFT JOIN Taxon_version AS TV ON TV.Taxon_Version_Key = TLI.Taxon_Version_Key LEFT JOIN Taxon_Common_Name AS TCN ON TCN.Taxon_List_Item_Key = TLI.Taxon_List_Item_Key LEFT JOIN Taxon_Version AS TV2 ON TV2.Taxon_Version_Key = TCN.Taxon_Version_Key LEFT JOIN Taxon AS T2 ON T2.Taxon_Key = TV2.Taxon_Key
LEFT JOIN Taxon_List_Item AS TLI3 ON TLI3.Taxon_List_Item_Key = TLI.Preferred_Name LEFT JOIN Taxon_Rank AS TR3 ON TR3.Taxon_Rank_Key = TLI3.Taxon_Rank_Key LEFT JOIN Taxon_Version AS TV3 ON TV3.Taxon_Version_Key = TLI3.Taxon_Version_Key LEFT JOIN Taxon AS T3 ON T3.Taxon_Key = TV3.Taxon_Key WHERE TLI.Taxon_List_Version_To IS NULL

It looks like something in the dictionary, which is to do with user added taxa.  If it is it is some combination of things we haven't come across before and probably just one taxa causing the whole problem. I will run some more tests in a minute and get back to you.

Mike

I think is a space issue somewhere. Either in the SQL Server configuration or in the available space on the  file server.  Your problem with Index_taxon_name seems to be down to having an Index on Taxon_List_Item_Key, which shouldn't be there. I am trying to establish how this could have got there - possibly it was there once and an upgrade has failed to remove it. I think it will be safe to remove the index, but have asked JVB to confirm my thinking.