1 Topic by Kate A

  • Kate A
  • Member
  • Offline
  • Registered: 22-07-2013
  • Posts: 11

Topic: Error on 'Detailed Species List' report

I attempted to run a 'Detailed species list for a single location report' (V06-12) and after choosing location and clicking OK, the following error message occurred: 'There is an error in the SQL code. The error message is 'string or binary data would be truncated''.

Having tried running the report a couple of times using different locations, I spoke to Simon Wood at WBRC who tried to run it on their system and he also received the same error message so I assume there is a problem with the report itself. Have you got a fix for this?

I am a beginner on Recorder so would appreciate clear step-by-step instructions, thanks.

2 Reply by andy_f (edited by andy_f 21-01-2015 17:10:21)

  • andy_f
  • andy_f
  • Member
  • Offline
  • From: BBOWT, Oxford
  • Registered: 06-12-2012
  • Posts: 40

Re: Error on 'Detailed Species List' report

Hi Kate,
might be a little late for you, but I've just spent some time figuring out why this and similarly derived reports don't work any more so I can update all of our species lists.  Might be helpful to others, so here's the fix:

- find the offending .xml report in your /User Files/Reports/ folder
- open using a plain text editor (i.e. notepad or notepad++ or similar)
- search for lines that say "Sort_Order VARCHAR(20)" - there'll probably be 2, one for quick report from a context menu when a site is selected, and one for a default all sites
- change to "Sort_Order VARCHAR(30)"
- save file and you're done

I don't know when the sort order changed from 20 to 30 characters long - I'm on R6.22 Dict. ...028

Cheers,
Andy.

3 Reply by Kate A

  • Kate A
  • Member
  • Offline
  • Registered: 22-07-2013
  • Posts: 11

Re: Error on 'Detailed Species List' report

Thanks Andy, that's sorted it. Appreciate your help!

4 Reply by mark.wills

  • mark.wills
  • Member
  • Offline
  • Registered: 23-07-2009
  • Posts: 6

Re: Error on 'Detailed Species List' report

Hi there, following on from this error message, a user I help with R6 was getting the same error message on a 1km/10km grid square report he uses.  I took a copy of it, make the change suggested in this thread to the Sort_Order - changing VARCHAR from 20 to 30.  I tested it on my copy of R6 (V6.19) and it worked fine.  Problem solved I thought.

I emailed him the file and told him to place it in the User Files/Reports folder, replacing the xml file in there.  He reports that the same error message is still coming up.  He is using V6.22, so my question is whether there has been further changes to the database structure between 6.19 and 6.22 that would result in this error message coming up when this xml report is run against V6.22 but not when run against V6.19.

Many thanks
Mark

Ecological Information Officer
NEYEDC

5 Reply by andy_f

  • andy_f
  • andy_f
  • Member
  • Offline
  • From: BBOWT, Oxford
  • Registered: 06-12-2012
  • Posts: 40

Re: Error on 'Detailed Species List' report

Hi Mark

Are these reports that you are running?
LCDetailed Species List 1 and 10km.xml
LCDetailed Species List 1or10km.xml

or possibly something derived from the same code (written by Mike Weideli, 2007)?

They both run fine on my V6.22 installation (with Sort_Order 30 characters, which I'm guessing I increased from 20 when I updated other reports).  So if these are the same reports I don't think there's anything else specific to V6.22 that causes a problem.

I had a quick check, and I couldn't find any preferred or common names in the INDEX_TAXON_NAME table referenced in the report that break the character limits in the temporary table #Main_Results (125 characters - maximums found were 74 characters long).  Couldn't see any other obvious candidates as the limits for other columns seem pretty generous, though there is always the possibility that the user has some records in their database that break limits - Recorders over 4000 characters or Determiners over 200 characters!

I would suggest confirming that he has definitely overwritten the files with new copies/is opening right report, and if so does the error occur on every 1/10km square used as a parameter? If it works on some squares but not others that might suggest that specific records are in fact breaking character limits, or are a bit dodgy in some other way. Otherwise I'm stumped, and you need help from someone with more expertise than me!

I believe that the Grid Ref, Dates, Recorders and Determiners are the only user entered data used in these reports (i.e. maybe more likely to contain odd values?), with the rest either system generated keys or from the dictionary. Anyway, just for comparison, these are the character limits for the #Main_Results tables in my copies of these reports:

  TLI_KEY CHAR(16) COLLATE SQL_Latin1_General_CP1_CI_AS,
  Preferred_Original VARCHAR(125) COLLATE SQL_Latin1_General_CP1_CI_AS,
  GridRef VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS,
  Sort_Order VARCHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS,
  Preferred_Name VARCHAR(125)  COLLATE SQL_Latin1_General_CP1_CI_AS,
  Common_Name VARCHAR(125) COLLATE SQL_Latin1_General_CP1_CI_AS,
  Vague_Date_Start INTEGER,
  Vague_Date_End INTEGER,
  Vague_Date_Type VARCHAR(2) COLLATE SQL_Latin1_General_CP1_CI_AS,
  Recorders VARCHAR(4000) COLLATE SQL_Latin1_General_CP1_CI_AS,
  Determiners VARCHAR(200) COLLATE SQL_Latin1_General_CP1_CI_AS

Good luck!
Andy

6 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,620

Re: Error on 'Detailed Species List' report

Sort Order seems the most likely reason for the problem.  Make sure if using notepad that you use 'Save As' and add the extension .xml   Otherwise by default it will save as a .txt file.

Mike Weideli

MikeWeideli's Website