Report Hanging during Query

[BCola]

HI All,

I am running into something odd. First, some background...I have been running crystal 8.5 with external .rpt files called from a VB Client (users can request various reports and each is exported to a network location for viewing as a PDF). I recently upgraded the VB Client preferences to use the 9 runtime. My new report uses OLEDB connection to the database and has a main stored proc along with 2 stored procs (for each of the 2 subreports). Here is my problem....

During testing, I have been running various reports and it seems fine. Then, it stops working and just "hangs". From that point on, I can't run the report from the VB APP or within Crystal itself. Here's the weird part...If I look in SQL Server, it thinks the stored proc is executing. Also, I can execute the stored proc from Query Analyzer and it takes about 1 second...this whole time, crystal is hung and memory and scans are very high on the database. To make matters worse, its inconsistent...it will suddenly just start working again.

If anyone has any ideas, I would very much appreciate it.

Thanks,

Brett

 

[synapsevampire]

If the database thinks that it's still running the SP, then it sounds like your database is at fault. I'm basing this on the fact that you understand how to properly perform a trace on the database and determine that the SP has not completed.

-k

 

[BCola]

Yes, we have looked into Enterprise Manager and the process info says the stored procedure is still running (runable) and executing.

The reason I'm not 100% convinced that the database is the problem is that I can exec my stored procedures manually in query analyzer. All three return in about 7 seconds.

Does Crystal execute the stored procs in order? Meaning the main report, then the first subreport then the next subreport in order they show up in the report (my two subreports are in the footer (a and c)?

It just seems like the queries will "sometimes" hang when they are run by crystal.

 

[synapsevampire]

Not sure what looked into Enterprise Manager means, but you should use the Trace facility (Tools->SQL Profiler) to determine who/what/where/when and how something is running, and how long it took.

The hanging may be the result of record locking too, there's lots of coefficients.

-k

 

[BCola]

I think maybe I'm not making my point.

If I exec my stored proc in Query Analyzer, it returns in 3-5 seconds.

If I copy the exec statement out of Crystal (Show Query) and run it in Query Analyzer it comes back in 3-5 seconds

If I trace it, both the execution plan and the T-SQL call look as expected and show as running.

There are no locks because I can run the procedure in Query Analyzer while its "hung" in crystal (and ofcourse, it returns the expected # of rows in about 3-5 seconds.

 

[synapsevampire]

Perhaps I did misunderstand you, I assumed that "If I look in SQL Server, it thinks the stored proc is executing" meant that the SP has not yet obtained the result set.

Anyway, I'd try using ODBC connectivity and see if that helps, I've had some similar experiences with OLE DB on occasion.

-k

 

[pgtek]

Hi

I ran into this problem when i was creating report against a Ingres database. It was not bad if run on crystal but to vb it would hang.
I move the data to a sql server and wow what a difference.
To speed up take your store procedure and check your links to have them as possible to equal join, left outher dose take longer to run or create views to filter more the time to run your query.

hope this helps

pgtek

 

[N11689]

We are another user experiencing this same problem. We have two separate SQL databases. The same set of reports is used for both databases. We use OLEDB for SQL Server to connect to the databases.

For one database, everythings works fine. For the other database we experience this hanging problem...exactly as what BCola described in the initial issue submitted here.

The stored procedure completes quickly when running it in SQL Query Analyzer. But, the report hangs.

Please help we are not sure how to determine the differences between the databases. Why is it running okay from one database, but hanging on the other?

 

[crystalempress]

We too are having this 'hanging' issue. We have put traces on the database to trace for queries that run over 15 seconds and this stored procedure never shows up -- yet we look at the report success on eportfolio and it says it took 8 minutes. We are using cr8.5 and ce8.5 using SQL server 2000. We've changed the report to ODBC connection and didn't seem to help. Our eportfolio set up with 2 servers(for load balancing).

When viewing this report is hits our time out of 20 minutes and the screen just sits there blank. When scheduling the report - it shows the success after 6 minutes but then when try to actually view the completed report in history it takes a long time to bring it up. We are getting really frustrated. Running the query in query analyzer brings it back in just seconds???

 

[N11689]

I forgot to mention that we are using Crystal 9 and we are on SQL Server 2000, service pak 3.

We didn't experience the problem in Crystal 8.5 (not that it doesn't exist there).

 

[N11689]

We were able to resolve the problem as follows:

The version of Crystal 9 Reports we use to develop reports is 9.2.0.448 (the upgrade cd)

The version the Service Pack 2 installs is version 9.2.3.787. This is also the version that was installed by the recently purchased Crystal 9.

1. There is a restore.exe that is installed with Service Pack 2 that will allow you to restore to your previous version of Crystal 9 provided none of the files have changed.

After running restore.exe, the reports work again.

Restore.exe is located in:
C:\Common Files\Crystal Decisions\Crystal Reports 9\Patches\Crystal 9.0 Service Pack 2
2. We narrowed the problem down to file crdb_ado.dll
located in C:\Program Files\Common Files\Crystal Decisions\2.0\bin
The crdb_ado.dll file that we install is version 9.2.0.512
The version installed with Service Pack 2 is 9.2.1.529

We found that if we overwrite the newer file with the older file, the reports work again. However, we don't know what other implications this will have since there are several other new files that were installed with the Service Pack.

Also, the Crystal Decisions web site lists some problems with accessing tables via OLEDB if the database logon information is changed.
Although it does not list the error message we get, this is exactly what we do when we run a report. We downloaded a hot fix from Crystal which is supposed to fix this, but it did not work.