Hi Sarah,

Thanks for the info.

I have rebuilt the indexes, as I also ran the Dictionary update the other day. Having just tried it again I get exactly the same error message as before, even running it in different ways, also using xml reports.

Having done some further investigating by reporting using Taxon Name instead, I had a look to see if anything looked funny and one stood out: x Dactylodenia st-quintinii which is a cross: Fragrant x Common Spotted Orchid. Removing that in Additional Filters the report then runs okay, so this is the culprit.

So, is this species an error by not being in the ITN table, if it is not in there that is?

Cheers

Brian

Thanks Sarah,

Is this "=" something I could remove myself in the meantime or should I wait until an update if that is the result?

Cheers

Brian

Hi Sarah,  I have had a similar problem to Brians

When trying to report on a survey the following Error occured.

Exception occurred in application Recorder 6 at 17/12/2007 12:14:43.
Version : 6.10.4.120

Exception path:
EOleException : String or binary data would be truncated
TExceptionPath : An error occurred executing the following SQL:
UPDATE "#REPORT_OUTPUT" SET #REPORT_OUTPUT.[Taxon Latin Name] = Index_Taxon_Name.Preferred_Name FROM #REPORT_OUTPUT LEFT JOIN INDEX_TAXON_NAME ON INDEX_TAXON_NAME.TAXON_LIST_ITEM_KEY=#REPORT_OUTPUT.LIST_ITEM_KEY AND INDEX_TAXON_NAME.SYSTEM_SUPPLIED_DATA=1 WHERE #REPORT_OUTPUT.TYPE = 'T'

I have had this error many times when trying to report on large amount of data.

I managed to isolate it to one species name: Philadelphus coronarius x microphyllus x pubescens = P. x virginalis,
Taxon list Item Key NHMSYS0001753997

From your replies to Brian the = sign appears to be the problem can this not be substituted with a symbol that looks the same but is not an asci symbol. For example Times New Roman character unicode: 2550 which looks like this ═
It is unlikely that the NHM or BSBI will alter thier species data as the format for hybrids seems to be used universally.

Best Wishes

Keith

Keith Cunningham ( Volunteer )
Durham Biodiversity Data Service
Hosted by Durham Wildlife Trust

I've done some further research into this and I'm not so sure that the equals sign is to blame. I think the problem is to do with the length of the taxon name, which in the above example is 69 characters long. I've traced the SQL that generates the temporary #REPORT_OUTPUT and it looks like this:

SELECT Distinct Taxon_Occurrence.Taxon_Occurrence_Key AS Occurrence_Key, 'T' AS Type, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SURVEY_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SURVEY_EVENT_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SAMPLE_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as LIST_ITEM_KEY , Convert(varchar(60), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Taxon Common Name], Convert(varchar(60), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Taxon Latin Name] into "#REPORT_OUTPUT" From ((((((Taxon_Occurrence LEFT JOIN Sample ON Taxon_Occurrence.sample_Key = Sample.Sample_Key ) LEFT JOIN Taxon_Determination ON Taxon_Determination.Taxon_Occurrence_Key = Taxon_Occurrence.Taxon_Occurrence_Key ) LEFT JOIN Survey_Event ON Survey_Event.Survey_Event_Key = Sample.Survey_Event_Key ) LEFT JOIN Index_Taxon_Name ITN ON (ITN.Taxon_List_Item_Key = Taxon_Determination.Taxon_List_Item_Key and ITN.System_Supplied_Data=1) ) LEFT JOIN Index_Taxon_Name ITN2 ON ITN.Recommended_Taxon_List_Item_Key=ITN2.Recommended_Taxon_List_Item_Key ) LEFT JOIN Taxon_List_Version TLV ON TLV.Taxon_List_Version_Key = ITN2.Taxon_List_Version_Key ) LEFT JOIN Taxon_List TL ON TL.Taxon_List_Key = TLV.Taxon_List_Key WHERE TL.Taxon_List_Key = 'NHMSYS0000436459' AND ((SURVEY_EVENT.VAGUE_DATE_START >= 38353 AND SURVEY_EVENT.VAGUE_DATE_END <= 39082))

Note where it says:

Convert(varchar(60), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Taxon Latin Name]

It also does this for [Taxon Common Name]. This is just a clever way of creating an empty field called [Taxon Latin Name] in #REPORT_OUTPUT using SELECT INTO. The important bit is the varchar(60). When the report wizard code then tries to insert a 69 character string into a 60 character field, it blows up, hence the "String or binary data would be truncated" error.

There are 74 rows in TAXON containing ITEM_NAMEs >60 chars, including our friend Gymnadenia conopsea x Dactylorhiza fuchsii = X Dactylodenia st-quintinii

I did actually replace all of the equals signs in TAXON.ITEM_NAME with tildes (~) and the problem remained (after rebuilding the index), which led me onto this further research. I've changed them back now. For now the only work around I can think of is to locate any records that point at taxa with names >60 chars and temporarily uncheck them. I've not tried this yet. That's for tomorrow...

I just tested this theory using the taxon Ctenophthalmus nobilis subsp. nobilis x nobilis subsp. vulgaris and it does indeed cause the Report Wizard to crash.

Hi Charles

Many thanks for looking into this and discovering the true cause of the problem!

Speaking to John from Dorset Software this is relatively trivial and we can provide a fix for this in the next version (via an update script).

In the mean time however if you have Enterprise Manager or Management Studio you can run the following script - note this fix has not been thoroughly tested yet and you should back up your database before proceeding;

UPDATE Report_Field SET Field_Size=75 WHERE Report_Field_Key IN
('NBNSYS0000000070', 'NBNSYS0000000071', 'NBNSYS0000000073')

UPDATE Report_Field SET Field_Size=80 WHERE Report_Field_Key IN
('NBNSYS0000000072')

You should now be able to run the report/s that were previously failing (I've just done a quick check and seems ok).

Please let me know how you get on.

Best wishes,