We are working with a document in which the query is very large. It has 138 objects. The query runs in Full Client, fails in ZABO on the same machine, with the infamous Bug window, Unhandled exception code c0000005
When we reduce the size of the query to 84 objects, it runs fine in both environments.
The size of SQL is 16k and 11k respectively.
Database is Oracle, clients 98 and 2000
Anyone seen a problem like this?
Thanks.
JF Cayron 
(BOB member since 2002-08-15)
Dave saw it first 
.
Cindy Clayton (BOB member since 2002-06-11)
Logged as a bug. No word on when it will be resolved… 
Dave
Dave Rathbun (BOB member since 2002-06-06)
Have you got the Bug#, or description (limits etc.)?
JF Cayron (BOB member since 2002-08-15)
You have basically found it. ZABO can’t handle queries with more than 80-ish objects. I thought it was 83, you said 84, so it’s somewhere in there.
If you refresh the same report via Infoview you’re fine. Full client, and you’re fine. ZABO is the only problem.
Just curious… what database are you connecting to?
I’ll see if I can find the bug number and post it.
Dave
Dave Rathbun (BOB member since 2002-06-06)
So it is the number of objects that is the problem? Not the size of SQL, then.
That’ll be Oracle, Sir
Thanks Dave
JF Cayron (BOB member since 2002-08-15)
I was curious about the database… the client where I first encountered this was using DB2. Here’s the bug info:
It is a bug #1047682 Detail below (25-Sep-2001 00:44)
ZABO crashes when running a query based on 85 objects and more.
Workflow to reproduce:
1- On Designer, Open the universe in attachment (based on tables of a repository), and modify its connection. Export it to the repository.
2- On Zabo 5.1.2 or Zabo 5.1 last build, Open the documents in attachements as
- Doc302249857-84 objects.rep
- Doc302249857-85 objects.rep
- Doc302249857-86 objects.rep
3- With the document based on 86 objects, when opening or running the Query Panel, Zabo should crash.
4- If you follow the same workflow with Full Client Reporter, all works fine.
This bug is scheduled to be fixed in the next major release. Looks like 6.0 is when it will be fixed.
Dave
Dave Rathbun (BOB member since 2002-06-06)
More details (21-Sep-2001 16:10)
after some exhaustive testing, here are some further details
1. The number of objects allowed in a 5i query appears to be 85. With 85 or fewer objects the query runs fine. With 86 or more the exception occurs.
2. The error is repeatable in a universe using a DB2 connection or an Oracle connection.
3. The error is not repeatable using a personal data source (an Excel file with 90 columns of data).
4. The type of object (dimension or measure) does not matter; any 86 objects of various types will cause the error.
5. The number of rows returned does not matter.
6. The page orientation (landscape vs. portrait) does not affect the error
Any query written with 86 or more objects will work in Full Client, but throws an exception when refreshed in ZABO/5i.
Those were notes we added to the case when we were testing. More details to pass on to your report writers / users.
Dave
Dave Rathbun (BOB member since 2002-06-06)
Thanks, Man.
That does not fix the problem but at least we know why!
It seems to me like a major one, I wonder why they do not attempt to fix it earlier.
JF Cayron (BOB member since 2002-08-15)
It probably depends on where the problem lies. If it is something in the underlying architecture for ZABO, and requires a major rewrite, I can see where they would wait. If it’s something as simple as redefining a variable type or something, you would think it would be done sooner.
They won’t tell us, of course, where the problem lies. 
The solution, in our case, was to create the report in Webi and refresh via Infoview. The user was essentially looking for a “dump” of a table, and was grabbing every object from the class, downloading it, then exporting to Excel. Since Infoview provides the capability to download a CSV, and it doesn’t have the limitation on the number of objects, it was a perfect solution for us. At least in that one case. 
Dave
Dave Rathbun (BOB member since 2002-06-06)
It makes me wonder if the SQL limitation in ZABO is 16k, not 32K.
Are these ZABO created/generated reports, or can you also paste this in Freehand SQL and get it to work?
Also try off a table with aliased column names to see if it’s a sizing issue. (Create a view and generically name the columns A-ZZ, etc).
-RM
digpen (BOB member since 2002-08-15)
I don’t think so. We tested with a basic Oracle table (once we encountered the problem with DB2) and the column names were c1 through c90. That’s about as short as they can be, and they were certainly shorter than the DB2 columns. Plus, if it were a SQL limit, you could replicate the same problem by creating a bunch of conditions. That isn’t the case. It seems to be purely in the size (width) of the result set.
Dave
Dave Rathbun (BOB member since 2002-06-06)
Well, I do not have time to test it, but the size of the query is well below 16k and it still fails. The number of objects seems to be the actual limitation.
With our particular query, we found 84 objects works, 90 fails.
Unfortunately, it is just a complex document, we have to have it in reporter, and so, in ZABO.
We can work around, since we have approx. 50 dimensions and 80 measures. We will split the query in 4 with each all the dimensions, and some of the measures. Not very clean, but it will get us there.
JF Cayron (BOB member since 2002-08-15)
You’re probably right about the SQL length if that’s the case, but that leads me to wonder about the length of the returning result set. I may give it a go by using ZABO to select a number of 255 character fields to see if the microcube is falling apart due to the expected result size. If so, that’d really be the limitations, not the number of objects. It’ll make it Harder to make up for, but at least they won’t design a patch that’ll fail if and when larger objects are used.
-RM
digpen (BOB member since 2002-08-15)
It is worth testing, but it would be a different problem. Reporter crashes BEFORE the fetch phase. (the query lasts 2 minutes, but the crash occures just seconds after the refresh command. The number of objects is definitely an issue.
JF Cayron (BOB member since 2002-08-15)