connectivity problem with crystal reports.

[Former Member]

Hi I am new to crystal reports. I've a connectivity problem with my oracle dashboard server. The problem is " I open crystal reports->database expert->create new connection->oracle server. In that I've given the TNS name, Username and password". It is trying to connect more than 7 hours. Never connected and never failed and no error. But the size of the DB is huge, really huge. I dont have the privilege to see the size of the DB. Do we have to set up any configuration or is there any other option to achieve calling a package with ref cursor in crystal reports. Please help.

Thanks & Regards,

Veeraraghavan M.

Message was edited by: Ludek Uher

[Former Member · Accepted solution]

Hi Veeraraghavan,

I think the other option to connect the Oracle for OLE DB Provider because size of the DB is huge.

Check this link: https://community.landesk.com/support/docs/DOC-20781

Thanks,

DJ

[former_member183750 · Accepted solution]

Thanks for the link

One thing to keep in mind; CR is a 32 bit report writer and thus requires 32 bit database connections. I scanned over the referenced doc and see that both 64 and 32 bit clients are being used. A 64 bit connection will not work, be it in the main report or a subreport.

- Ludek

[former_member11696 · Accepted solution]

Actually for CR using OLE DB connection simply puts another layer on top of the client so you can see performance issues. That Document is for configuring Land Desk which may like OLE DB better, the 32 bit response is correct, CR Designer can only use the 32 bit client.

Better to use the Oracle Driver you selected.

Ludek is also correct, when CR queries the DB server it tries to get a list of all of the options select in the screen shot Ludek posted. This uses system resources, and a lot of it, to build the table list for CR.

For huge Oracle databases is has always been this way.

Your only option is to add filtering in the Table Name Like and Owner Like to reduce the amount of info coming from the Server.

Creating Views or stored procedures is an option also.

Also, the reason CR is just sitting there is because it is trying to collect the info but odd that it did not time out. The client must be keeping the connection valid.

Don

[Former Member]

Thank you for your support Don,Ludek and Dhananjay. I've fixed it on the same day I raised this request.

I did it as per Ludek screen showing filter for schema and objects but a slight difference in it.

Still I need some clarity in it.

The problem is having a DB with huge volume taken enormous time to load in crystal reports and went to not responding.

The way I solved it is below.

I use "Oracle Server" driver in crystal reports

Step 1: created a dummy report pointing to an oracle database which has less number of schema's and objects.

Step 2: Closed and reopened crystal reports. Then I opened that dummy report in it. It prompted for the password.

Step 3: Instead of giving password I've changed the service name, username and password of the huge DB which i need to connect.

Step 4: Now in the connection tree it creates the DB with no objects. when i tried to explore, its getting hang again.

Step 5: Instead exploring, I right click it--> options(screen shown by Ludek) and given my schema name in owner name like field.

It instantly loads that schema which i given in it.

This is helpful for me to proceed with the development. But lets say if i don't have a DB with less number of schema's and objects. how do I connect it?

The Huge DB that I referred is not dedicated for reports alone. It contains all the application schema's. basically a test environment.

The thing which I want to know is

1. Is there any option to filter schema at the time of establishing new connection. So that it does not load all the schema's?

or

2. What will be the permanent solution for this to proceed in a straight forward way instead doing the workaround steps above?

Please help.

Thanks & Regards,

Veeraraghavan M.

[former_member183750]

Sorry, I'm a little unsure about what was done with that db options screen.

As Don mentioned, by using that screen you should be able to add filtering in the Table Name Like and Owner Like to reduce the amount of info coming from the Server.If you do not need views, get rid of those in those options. Perhaps removing the "Sort Tables Alphabetically" will also help. Do this with the report that was going against the smaller db. I believe that the options saved here are global. E.g.; they will apply to all subsequent reports.

- Ludek

[former_member11696]

Hello,

Open CR Designer and click on File, Log on or Off Server... and you'll get the Data Explorer screen.

Click the Options and you can add your filtering there. Then connect, and create your reports.

Don

[former_member183750]

A good place to start would be with a few searches - search box is in top right corner. Use simple search strings like 'crystal oracle tns', see what comes up

Once you have done a few searches and if you have not found a solution let us know:

The version of CR you are using.

Then the version of Oracle.

What you tried to do based on the searches you have done.

One thing you may want to do is limit the number of tables the report engine is attempting to bring in. Look at File | Options | Database tab:

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter