Crystal Reports keeps open too many Oracle DB sessions when running the report

[paul_lazaroni]

SAP BusinessObjects Crystal Reports 2013 Support Pack 5. Version 14.1.5.1501

We have an issue when running a report created using Cystal Reports 2013 and running the runtime against an Oracle database server 12c. The report use 4 embedded SQL commands to populate lists, the values selected for each list will be used as input parameters for the main Stored Procedure that retrieves the data from the database. The issue is that when using embedded SQL commands to populate the input lists the Database keeps open too many sessions per one single report execution. In this case we have 4 embedded SQL commands to populate 4 input lists, 1 main SP and 2 subreports SPs, in the Oracle server for each single report execution there are 25 sessions opened.

Is this an issue with SAP Crystal Reports 2013?

Is there any way to avoid Crystal Reports to open so many sessions per report execution?

Best Regards,

Paul L.

[DellSC · Accepted solution]

This is just the way that Crystal works. If possible, I would take the commands and use them to create views in the database that you can connect to as if they were tables. This should decrease the number of connections because it should just open one for the parameter lookups.

For the subreports, Crystal will connect to the database each time the subreport runs - the connections are not carried over to successive runs of the subreport.

-Dell

[paul_lazaroni · Accepted solution]

Hi Dell,

Thank you very much for your help. Do you know if this Crystal's behavior, the way that Crystal works with commands, is explained in any document?

Best Regards,

Paul

[DellSC · Accepted solution]

I don't know whether it's in any documentation. I'm not an SAP employee, I've just been working with Crystal for a long time (@20 years) and reading/responding to questions on the forums for almost as long, so I've seen just about every type of Crystal question possible, including questions like yours. Maybe don.williams can point you to some documentation on this.

-Dell