Crystal Reports Column Limit 2

[fificat]

It appears there is a limit that Crystal Reports 9 has to the number of columns it can select from a SQL stored procedure(ie just over 180).

Is anyone aware of this problem?
Is there a resolution?

I'm looking to use upwards of 2000 (no joking).

 

[dmeakin]

Thats a lot of colums, maybe a cube would help

 

[fificat]

Not possible - I'm the monkey not the grinder!

 

[Turkbear]

Hi,
Not to be too nosy, but what kind of data model would end up with you needing to use 2000 columns?

What database?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

<laffin>

Seems an odd report to me, what users are spilling through 2000 columns? But then I get plenty of requests for weird reports...

Perhaps you should consider using the database itself to output such things, it sounds like you're doing this to be used as an import by another application.

Also, if you're experiencing an error, post the error and the database/version/connectivity used.

You may find that this is a limit within your connectivity, not Crystal.

-k

 

[fificat]

Error Message
‘22018:Microsoft][ODBC SQL Server Driver][SQL Server] Syntax error converting the nvarchar value ‘%’ to a column of data type int.’

Issue Description:

Multiple reports of which the current one is trying to link crystal report to SQL stored procedure containing circa 300 columns.
It seems that if I comment out a number of rows in the SP and connect to the SP it is OK on some machines but I get the error message on some other machines.
Also on some machines it works on, sometimes it connects and sometimes we obtain the above message.

2000 columns (MI Report that is due to be started next week) is because the report has a large number of sumarised fields in a rolling 4 month report (trend analysis)with 3 levels of drill down.

Any help much appereciated

 

[rosemaryl]

Dear Fificat,

The error indicates that at some point in your Stored Procedure it is trying to use a character (varchar) and cannot convert it to an number (integer).

You state it runs on some machines and not on others.... and so on.

However, you did not state your testing methodology.

Now, why could it happen ... it could be a number of reasons:

Most likely: You use some database field that you thought only contained numbers, but in reality since it is really a varchar field it can contain text data and whenever your sp hits that field, it abends with the above error.

I wouldn't presume to think it is the number of columns. While I have never approached that number in an sp I certainly have in reports based on views and Crystal shouldn't care.

To me, a more valid test would be to test the sp in SQL Query Analyzer using the same parameters you are using for the report and check what happens.

Also, when I don't get the results I expect ... I look to the data itself. I would verify all of the columns I am using in my sp are of the datatype I think they are and would get it running without errors in Query Analyer before using it in Crystal Reports.

However, all of the above is guessing because I do not know what is in your sp. If you are not a SQL DBA maybe you

More info on what you are doing might be helpful.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

Still seems bizarre to me that a report even with 10 drilldown sections would use 2000 columns, that means that they're drilling into 200 columns pre drill down...again, very odd.

Anyway, the error seems pretty straightforward, youre ODBC driver is hiccuping at the contents of a field, this is not a Crystal error.

As Ro appropriately points out, you need to try a more controlled testing environment, and lookup what errors mean (search engines are a good way).

I would guess that you will be using a CAST or CONVERT against the offending field in the SP to resolve this.

-k

 

[mrees]

FifiCat,

I think I know what this is. Crystal can sometimes get a little upset if you have many columns with long names. Try reducing the length and try again. I don't know if this is a Crystal or ODBC problem though.

I appreciate what others have said about the number of columns, but as report writers we can sometimes get caught between what the user wants and what the data owners are prepared to give us!

mrees

 

[fificat]

Thanks to all of you.

I 'Aliased' all of my column headings and I am no longer receiving the error message.

FYI the column with the longest heading was 28 characters long. And you are right - the column headings are not our choice as is the number of fields required.

Thanks again.

 

[rosemaryl]

Dear Mrees,

Very good catch!*. I will add that to my own kb so I don't get caught out.


Thanks,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[synapsevampire]

Interesting, what database/connectivity are you using?

-k

 

[fificat]

Synapsevampire,

We're using 'SQL Server ODBC connection'. Crystal Support suggested (finally come back with an answer) the following:

http://support.businessobjects.com/library/kbase/articles/c2014614.asp

from there KB buty it refers to registrry settings which we are not able to tinker with.

-fc

 

[synapsevampire]

Hmmm, that does sound a tad kludgey on their part, glad that you resolved it.

-k