1 Topic by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Topic: Less than helpful error message

I am having a problem with one of my xml queries, which ought to be easy to solve, were it not for the opacity of the error message...

Essentially the xml is a version of one of the supplied polygon queries, returning recods for a mapped polygon. I have modified it substantially so that it produces output in a very specific format to meet our needs.

Normally the query works fine (we usually use it on relatively small polygons where the number of record recieived is fairly small. Today though we ran it on a very large polygon and after a lot of thinking it produced a dialog with the message:

"There is an error in the SQL code. The error message is 'String or binary data would be truncated'"

However, if I carry out the same query via the wizard, using the same polygon and specifying exactly the same output fields, the query runs through to completion and returns 87,000 records.

Looks like I will now have to spend a lot of time examining my xml and comparing what it does with what the wizard does to try to locate the problem, or alternatively searching the 87,000 records across all fields, trying to find what might be truncated.

Would there be any point in requesting that the error message might be improved to give just an slight clue as to where the error is?

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

2 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,384

Re: Less than helpful error message

Are you defining any temporary tables? When I get this error it usually means the length of a varchar field in a temporary table is too short.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

3 Reply by MikeWeideli

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

Re: Less than helpful error message

I have only seen this message where a temporary table is being used in the XMl report to hold interim results. If this is the case I would think that one of the columns in this is not long enough. You could increase the size of any suspect ones to see if this affect the results.  Any columns which are concatenated (eg Recorders, Abundance) are likely to be the culprits.

Mike Weideli

MikeWeideli's Website

4 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Less than helpful error message

Thanks Mike. There is indeed a temporary table and I had fairly recently modified the field sizes as we had been having problems with the combined size of all fields exceeding 8000 characters (I think, I had never come across this  rectriction before).

Again I am not quite up to speed after the holiday, but when I next get round to this one I will have another look and let you know. I do recall that the problem was happening only sometimes and was more likely to happen with a larger search plygon (i.e. where there was more data returned)

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

5 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Less than helpful error message

OK, I have given it a bit more time now and am none the wiser. The first step was to remove the taxon occurrence comment, which is text data type, but this made no difference.

Next I examined the ouput produced by the same query using the wizard. None of the fields I was able to check produced a value which was longer than the number of characters specified for the corresponding temporary table field (including the various concatenated fields).

There are a few additional fields in the temporary table which are not output by the wizard, but I can't see any of these which look likely to cause a problem.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

6 Reply by RobLarge (edited by RobLarge 03-01-2013 12:56:46)

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Less than helpful error message

Ahah!

A breakthrough. I have been through and manually checked everything and finally discovered the problem (I think).

The abundance field, which I had set at varchar(100), which seemed adequate, is populated using your function Mike LCFormatAbundanceData which is set up to return varchar(8000). I modified your code to produce varchar(100) instead and the query now runs without a problem.

As a double check I ran the query through, output and analysed the data generated (expecting one or more records to have truncated abundance data), but found that the longest string of abundance data output is 19 character long (which is why I hadn't spotted it before).

Seems odd that the truncation error I described is triggered by the potential size of the field, rather than by the actual size of the data being inserted. I can't see how this can happen.

Needs more thought I guess.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website