How to run reports concurrently in CE9? Help please.

[chenkang]

Hello,

We have around 50 reports developed in Crystal Report 8. We use "CR Oracle ODBC Driver 4.10" to connect Oracle 9i database, and use Oracle stored procedure which return a ref cursor as data source. We publish those reports on Web via URL. All report work fine in Crystal Report 8, however, we are not able to run more than one report at same time.

In order two support more than one concurrency user, we plan to upgrade our system to Crystal Enterprise 9. However, we found some problems during migration process.

1. We were able to run some reports at same time with crystal enterise 9. i.e., if we run report A first which may take 10 mins, then run report B which just take 1 mins, report B will come back before report A finish. We can see two active "pageserver" session at oracle side. However, not all reports work like that, most reports can only be run exclusively. If we run report A first, report B have to wait until A finished. We only can see one active "pageserver" session at oracle side even if we launch two report at same time.

2. All of our reports initially developed in Crystal Report 8. When we publish them via URL by Crystal Enterprise 9, some report, say report A and B can run concurrently. Since Report A got a "Query Engine Error" when we click "next" button, we re-compile (Verify Database) it in Crystal Report 9 and save it as Crystal Report 9 format. However, after we convert its format, Report A and Report B can' t run concurrently any more.

Any ideas?

Thanks for your help.

Kang

 

[JEX]

Based on what you have said above I can't see why CE wouldn't be able to process the reports simultaneously. Check your settings in the CMC for the Page Server - what is your Maximum Simultaneous Processing Threads set at?
(I'm assuming you are running these on demand)

J

 

[chenkang]

Yes, we run those reports on demand. For the page server, we have 75 "Maximum Simultaneous Processing Threads". When I call Crystal Decision technical support, they doubt that the ODBC driver "CR Oracle ODBC Driver 4.10" causes this problem. However, according to document "scr_oracle_stored_procedures.pdf", that is the only ODBC driver we can use if we use Oracle stored procedures which return a ref cursor.

Any ideas?

Thanks for your reply.

Kang

 

[JEX]

I don't have experience with Oracle but here are some things I'm thinking:

* Are both reports A and B running off the same stored procedure? In other words do the reports that hold up other reports from being processed have any in common amongst them, but different from the others?

*Save both reports as 9 and then run them on demand.

*Kick them off as scheduled reports as well - multiple times so that CE will process them simultaneously - about 5. See if this happens - check the Job Server Childs here - as the Job Server processes scheduled reports not the Page Server. This should help you try and narrow it down a little bit. If the Job Server can handle it then go back to concentrating on the Page Server.

*If the same occurs - start looking at the Oracle server.

*You may also want to try copying and pasting the stored procedure into the CR Repository and changing your reports to look at that as the datasource - see if it makes any difference.

These are just some ideas to help you narrow the problem. Sorry I can't be more specific.

You say you've spoken to Tech Support and they say they "doubt" it's the driver - do you have Tech Support with Crystal? If so, they'll keep the incident open until they sort it out for you.

cheers...J

 

[chenkang]

Hi, JEX

Thanks for your reply. Now we are kind of figure out what's the problem.

We can't run mulitple reports same time by pageserver even if they use different stored procedures. If we schedule them in CE and run mulitple times by Job server, everything is fine. We can get those reports run currently.

After talking to CR technical support, we changed the database driver from "CR ODBC for Oracle" to "Oracle Native Driver", and compile all reports in CR9, now they work well with page server, multiple report can run at same time.

Thanks.

Kang

 

[TheRealWelshy]

Hello

I have been experiencing the same problem in version 8.0 & 8.5 and trying to get it resolved since October 2002. CD Tech Support tell me that it should work using the "CR ODBC for Oracle" driver. But we have tried all sorts of fixes but to no avail.

Due to the large number of reports involved it is impractical to convert them all to the native driver so I am still unable to view on demand multiple reports at the same time.

If anyone has any ideas I would appreciate their feedback.

Welshy

 

[Turkbear]

Hi,
I never,never,never use ODBC if I can avoid it, and with Oracle, I can always avoid it by using the Native support built into CR - When designing the report use the
'More Data Sources- Oracle Server ' selection ..
Unless that has been changed somewhow in v9 ( we are on 8.5) I would recommend that to everyone..

 

[chenkang]

Welshy,

The Oracle Native driver didn't work properly after we covnert our reports to it from ODBC driver. Some reports were returned in a wrong format with wrong figures. It even made the Oracle DB instable.

Now we are testing another implementation. we wrote our own "pageserver" in Visual Basic, instead of multiple-thread, we use multiple-process to handle different report request, then we can run several reports at same time with ODBC driver.

Hope this help.

Kang

 

[Turkbear]

Hi, That's very odd behavior--I have converted dozens of ODBC based Oracle reports to the Native Driver and have not encountered any problems..Did you do a 'verify database' after changing the location of the report's data?

( But, then again, that's why computers are so fascinating, and why those of us in IT can usually keep our jobs)

 

[chenkang]

We did verify the database. Actually we even can't convert it at beginning (CR 9 doesn't allow us to save the reports after we changed the driver), then we apply two patchs and was able to convert the driver.

Maybe it because our reports to complicate? we use oracle stored procedure as data source, some stored procedures have more than 2000 lines.

Kang

 

[Turkbear]

Hi,
Even odder, I would expect the Native Oracle connection to handle SPs better than ODBC...

Oh well...