Egad! Zounds! Can this be true?

Why?

SQLCODE = -674, SQLSTATE = 09W07, ERRORMSG() = Statement's size limit is invalid

"The size limit must be a constant integer greater than 0 and less than 32767."

http://dcx.sybase.com/index.html#1101en/saerrors_en11/errm674.html

...same thing in V12 docs.


Yes, I actually got the message... not sure yet what the TOP value actually was, still trying to get over the Doc Shock!

asked 20 Oct '10, 16:06

Breck%20Carter's gravatar image

Breck Carter
26.2k430599862
accept rate: 20%

edited 28 Oct '10, 07:48

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665

2

I think this is a mistake in the docs. I tried "select top 100000 * from systable" with SA12 and did not get an error. I checked the code and it would appear that at one time there was an upper limit check but it has been removed. I will double check and then get the docs fixed.

(20 Oct '10, 18:48) Mark Culp

BTW: How did you get the error? What version/build & what tool did you use? The limit check was removed in 2003.

(20 Oct '10, 18:53) Mark Culp
Comment Text Removed

@Mark: I haven't had time to check, but I suspect it was SELECT @top with a variable that was NULL... that would do it, right? :)

(22 Oct '10, 09:16) Breck Carter

The problem in dbisqlc stems from the database server reporting that it "definitely knows the row count for this query will be 26" (or whatever value was in the TOP phrase) by reporting a positive value in the SQLCOUNT field of the SQLCA. dbisqlc is easily fixed by just never letting it trust the count from the server (in which case is does just a little bit of extra work); however, I also wonder if trusting the TOP clause represents an server bug too.

permanent link

answered 22 Oct '10, 19:03

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

So this is a bug that will be fixed for dbisqlc in current versions?

(22 Oct '10, 19:28) Volker Barth

@John: "will not exceed 26" has more truthiness than "will be 26".

(23 Oct '10, 07:01) Breck Carter

@Volker: Maybe, under SAP's direction, dbisqlw.exe will be created... an actual Windows version of ISQL. I hate to say it, and I am not taking a shot at the developers, but as a corporate entity "Watcom Doesn't Give Good GUI". It's the only major exception to this rule that I know of: "Watcom Does Things The Way They Should Be Done."

(23 Oct '10, 07:05) Breck Carter

@Everyone: That was my Evil Twin speaking. My Good Twin is a big fan of the current dbisql.com/.exe... except that I never know which one to execute :)

(23 Oct '10, 07:10) Breck Carter
2

@Volker: Yes, assuming I've made the final change it should be fixed in 12.0.1GA, 12.0.0.2602, 11.0.1.2514, and 10.0.1.4139.

(25 Oct '10, 20:38) John Smirnios

@John: Could you post the CR number here? - You know, we love more documentation:)

(28 Oct '10, 07:47) Volker Barth
3

CR# 645986. It fixes the problem where dbisqlc may show a subset of the result set, fixes the reporting of DSNs in the Login tab (show DSNs from all SA verions, 64-bit used to not show any DSNs), and fixes the handling of connection parameters that did not have a short form (eg, the new "Server" parameter).

(28 Oct '10, 13:19) John Smirnios
More comments hidden
showing 5 of 7 show all flat view

I did remember having hit that 32k limit myself too. In my db code (v9 compatible) I see this:

// select top n has a max number of 32767 
StringBuilder sSQL= new StringBuilder(); 
sSQL.append("select "); 
if (rowCount >= 0 && rowCount <= 32767) 
{ 
  sSQL.append("TOP "); 
  sSQL.append(Long.toString(rowCount)); 
  sSQL.append(" "); 
}

I think the limit was raised in V10 and upward.

André

permanent link

answered 21 Oct '10, 09:18

ASchild's gravatar image

ASchild
777222740
accept rate: 14%

edited 28 Oct '10, 18:07

Siger%20Matt's gravatar image

Siger Matt
3.2k496697

As Mark has pointed out, that seems to be a doc mistake. I can use TOP and START AT values way bigger than 32.766 and get correct results, both when using constant values and variables.

I have tested with an 11.0.1.2427 engine and a table with 2.353.744 rows. Column 1 is the PK.

SELECT TOP 2353744 * FROM MyTable ORDER BY 1 ASC;
-- works, starts with first PK
SELECT TOP 2353744 * FROM MyTable ORDER BY 1 DESC;
-- works, starts with last PK

SELECT TOP 1 START AT 2353744 * FROM MyTable ORDER BY 1 ASC;
-- works, shows last PK
SELECT TOP 1 START AT 2353744 * FROM MyTable ORDER BY 1 DESC;
-- works, shows first PK

However, when the sum of TOP and START AT seems to be larger than the table count, the effect seems wrong when using dbisqlc (11.0.1.2452 and 12.0.0.2566):

SELECT TOP 10 START AT 2353744 * FROM T_VertragDuplikat ORDER BY 1 DESC;
-- works, shows first PK 
SELECT TOP 25 START AT 2353744 * FROM T_VertragDuplikat ORDER BY 1 DESC;
-- works, shows first PK
SELECT TOP 26 START AT 2353744 * FROM T_VertragDuplikat ORDER BY 1 DESC;
-- doesn't give an error but returns an empty result set -> wrong

The incorrect empty result set is shown for any TOP value greater >= 26. (Interestingly enough, dbisqlc 12.0.0.2566 starts to show the empty set for TOP >= 25.)

In contrast, when using DBISQL (11.0.1.2452 and 12.0.0.2566), any increate of TOP n does still show the result set with the first PK.

Resume: So the TOP problem seems to be restricted to dbisqlc.

permanent link

answered 21 Oct '10, 09:21

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

It also works correctly for 9.0.2.3480. I've tried
select top 1234567 start at 2345678 *
from mytable
order by id
and get no error, the result set looks good.

permanent link

answered 21 Oct '10, 15:47

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334583
accept rate: 11%

@Everyone: Yay! Everyone gets an upvote!

(22 Oct '10, 09:19) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×102
×19

question asked: 20 Oct '10, 16:06

question was seen: 1,560 times

last updated: 22 Oct '15, 08:32