output columns too long

[Former Member]

Hi,

I've a join over two tables with about 150 columns. This was never a problem.

Now I have two MAXDB databases from two customers (7.6.06.03). In one of them my statement says "output columns too long", running the same application on the second db works fine.

Any ideas?

I guess there is a problem with maximum record length of about 8000 bytes. Can this be extended in any way?

Any help will be appreciated.

[Former Member]

Thank you all for your help.

In fact I tried even further. I fixed the current problem by reducing the amount of some columns and also I did a redesign of to user functions. Output was 1000 chars, i reduced it to 200. Now it works fine.

I realized that SQLStudio has more restrictions than my own ODBC connection from inside my application.

Statements that doesn't work with SQLStudio are running without problems from my app.

Strange things are going on...

Thank you all, especially to sen for the detailed info on the limits.

@Lars Breddemann: do you suggest to move to 7.7? Are there any problems? How do i the update?

[lbreddemann]

>

> Thank you all for your help.
> 
> In fact I tried even further. I fixed the current problem by reducing the amount of some columns and also I did a redesign of to user functions. Output was 1000 chars, i reduced it to 200. Now it works fine.
> I realized that SQLStudio has more restrictions than my own ODBC connection from inside my application.
> Statements that doesn't work with SQLStudio are running without problems from my app.
> Strange things are going on...

Hmm.. this sounds a bit odd.

Do you use prepared statements in your application?

Concerning the change of limitations, just compare the documentation

[Restrictions for 7.6|http://maxdb.sap.com/doc/7_6/f6/069940ccd42a54e10000000a1550b0/content.htm]

>>> Internal length of a table row: 8088 bytes

[Restrictions for 7.7|http://maxdb.sap.com/doc/7_7/45/470c541fb54e2de10000000a114a6b/content.htm]

>>> Internal length of a table row: 32767 Bytes

  @Lars Breddemann: do you suggest to move to 7.7? Are there any problems? How do i the update?

Yes, absolutely.

7.7 has been out for years now and it's pretty stable.

To perform the upgrade, I'd go for simply using SDBSETUP.

Alternatively you may use SDBUPD (textbased interface).

Ok, now you've got a project for the early days in 2011

Have a nice holiday season!

Lars

[Former Member]

Wow! That's pretty cool! Thank you, that's a nice christmas gift.

To answer your question: Yes, I use prepared statements most the time. But the statement that caused the trouble wasn't a prepared statement.

Christoph

Edited by: Christoph Schwerdtner on Dec 24, 2010 12:52 AM

[former_member218672]

Hi Christoph,

Check for the following reasons which are possible:

1. The total length of the output columns exceeds 8084 bytes.

2. The total length of the output columns, together with the columns specified in the ORDER or GROUP clause, exceeds 8084 bytes.

3. The total length of the output columns, together with the correlated columns and columns of predicates that contain a subquery, exceeds 4048 bytes in an SQL statement with correlated subqueries.

Try below activities -

1. Reduce the number of output columns.

2. Try to do without sorting or grouping, or to perform sorting or grouping across shorter or fewer columns.

3. Try to manage with fewer output columns or correlated columns, or to achieve the desired effect using several SQL statements

Regards,

Sen

[Former Member]

Hi,

do we have different default-code.types (ASCII/UNICODE), causing char-columns to need double-size?

The client (Database Studio / JDBC / ...) is the same in both cases?

Elke

[lbreddemann]

>

> Hi,
> 
> I've a join over two tables with about 150 columns. This was never a problem.
> Now I have two MAXDB databases from two customers (7.6.06.03). In one of them my statement says "output columns too long", running the same application on the second db works fine.
> Any ideas? 
> I guess there is a problem with maximum record length of about 8000 bytes. Can this be extended in any way?
> Any help will be appreciated.

I'd do the same guess. No, the limit is fixed with 7.6 - but extended with >7.7.

Would you mind to post the table definitions and the sql statement?

regards,

Lars