Stored Procedures and Cursors vs. Crystal SQL Designed queries

[marabou]

Hi
I have an ongoing problem with what I think may be due to the fact i have got the wrong end of the stick and would appreciate some clarification.

I have two applications running from the same sql server database. We have had many problems with one of the apps where it was leaving the cursor open and as it is quite a large network with many users, many users were left hanging. However, we have been through the code and sorted that app out as much as we can. In the other app the only place where any errors like that can occur is in the report which gets run 300(avg)times a day (its a delivery note). This report will eventually be ran from within the second VB app, but at the moment it is being ran from Crystal Reports itself.

To do this report I have designed a query within CR SQL Designer and am using CR on the client machine to print the report out. The entire system is still under quite a lot of development even though we are live.

I have quite a few questions.
Firstly, can someone explain to me what CR does with its cursors.
Would I be better off using a stored procedure to run this query and how would I then go about putting this into a VB app.
If I used a stored procedure would I then be able to make sure it is not causing a prblem with SQL Server.

I am using CR 8, VB 6 and SQL Server 2000.

NB. I am unable to do the query using the normal CR report writing as it uses a union and that is impossible to do within the CR GUI.

Would appreciate any help whatsoever in this as I am really stuck. Thanks in advance.

 

[balves]

Crystal uses the lowest possible locking level since it's a read-only app. For SQL Server, that's READ UNCOMMITTED. Running a report shouldn't lock up resources for other users. Whether you have the capacity to run N reports at the same time along with whatever else is running on SQL Server is another issue. Whether the report retrieves records by indexes, not table scans, whether tempdb is big enough on your SQL Server box, etc., are things that the DBA should investigate.

You can do a UNION by editing the Database / Show SQL Query. Writing a stored procedure, however, is a much better alternative.

It shouldn't make a difference, but you can put

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

at the top of your stored procedure to make sure that you have the lowest possible locking level.