CommandTable returning as Procedure

[Former Member]

Hi all. Got something strange going on here. I'm writing an app that will search through the SQL in all of our reports (all of our reports are written using a single Command object). Some of the reports for some reason are returning their tables as CrystalReports.Procedure, rather than CrystalReports.CommandTable. Trying to cast it to the latter expectedly causes an invalid cast exception. I've double-checked the ones doing this, and there is only one table behind each report, and each one is indeed a Command.

While playing around for possible solutions, I ended up tidying up some of the code in one of the reports; I didn't make any real changes to anything, just prettied up the code some. One of the things I did was to replace any instances of 4 spaces with a single tab character. And suddenly, it's now seeing it as a CommandTable for that report. I'm assuming it has something to do with a size limit of the CommandText, but I can't find anything documented anywhere on this.

Is this a known issue, and is there any workaround, aside from cleaning up all the code? Main thing I'm concerned about is that if this is indeed caused by hitting a limit for the CommandText, doing some code beautification on the offending reports may work for some, but may not work for others with exceptionally long complex queries. Thanks in advance for any assistance.

[former_member183750 · Accepted solution]

What is the exact version of CR you are using?

What version of .NET?

Ludek

Follow us on Twitter http://twitter.com/SAPCRNetSup

[Former Member]

Thanks for the responses.

Version Info:

CR Developer, 11.5.10.1263

.NET Framework 3.5

CR Oracle ODBC Driver 5.1 (05.10.0017)

Let me know if you would like any of the exact version numbers for any of the actual libraries.

In this case, I wouldn't guess it's specifically parameters doing it. Usually we do use parameters in our command objects, but not always. For the ones that are giving the problem, the number of parameters varies anywhere from 0 to more than 5.

I agree with you Don that long term the ideal solution would be to move all of these queries into the database (thus alleviating the need for this tool in the first place); I suggested the same thing when I first got here, but not exactly a tiny undertaking (we have hundreds of reports). So yes, long term solution, this would be ideal, it's just not something we can approach at the moment; we may end up having to work towards this by attrition as reports get updated.

An interesting thought about the driver. I'll play around using different ones to see if there's any change and let you know what I come back with, although the app itself is not making any connection to the DB; it's just checking out the table, grabbing the command text, and closing up. However, I suppose it could be something with how the report itself is being saved based on what driver was used when the report was built. I'll follow up on this after testing.

[former_member11696]

Hi Ernie,

One more update, CR XI R2 Service Pack 5 only supports 2.0 Framework, you can find more info in the [platforms |http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/e0fbebaf-07e0-2c10-ad9a-d45810ae7109&overridelayout=true]pdf file. download Service Pack 6 and set your project for .NET 2.0 and test again. I'm not sure if this is going to resolve the problem but at least you are in supported areas.

Link to SP6: https://smpdl.sap-ag.de/~sapidp/012002523100015859952009E/crxir2win_sp6.exe

Another update that may fix the issue is to update the CR drivers to 5.3 from this link:

https://smpdl.sap-ag.de/~sapidp/012002523100008666562008E/cr_datadirect53_win32.zip

If you must use 3.5 framework you'll have to upgrade to CR 2008 and Service Pack 2.

Thanks again

Don

[former_member11696]

Hi Ernie,

Also need to know what DB driver/client you are using?

ODBC I believe has a limit somewhere around 32k, due to unicode double byte character limitations. This can typically show up also if you have a lot parameter values. The SQL generated will be for example: WHERE Yourfield = "YourValue" OR Yourfield = "YourValue" OR Yourfield = "YourValue" .... which can fill up the buffer quite quickly.

The error is likely due to CR not being able to parse the Query completely and thus returns the wrong type.

What you may want to do is push all of this processing to the Server which is much more efficient at doing so using a Store Procedure. Not enough info to be specific and if you have a DBA to discuss this with he/she should be able to explain how to optimize the SQL.

Thank you

Don