Sage 100

Situation:  Sales Order Detail file has 7.4 million records;  I have a crystal report that connects to the SQL DB using the SO Header and Detail files.  Is it logical that this Crystal report would be the culprit for 'locking up' the SO_SalesOrderDetail file?  I had the report running for about 45 minutes, and it was 2/3rd done.  As soon as I stopped the report from generating the SQL table freed up.  This just doesn't make sense -- why would reading data, especially from SQL and Crystal lock up the file and wreak havoc on the users?  

------------------------------
Jane Scanlan
Partner
Next Level Manufacturing Consulting Group
------------------------------

Look at the task manager performance.  It could be that Crystal SQL is using all of the available CPU time or Memory, leaving nothing available to other users.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

Jane – I'm guessing you are running this from the Crystal designer instead of Custom Reports menu. The SQL stmt Crystal forms is handled by the SQL Query Optimizer. Sometimes though based on how you joined and grouped the tables it can lock records temporarily. Classically seen with some MS Access queries on large SQL data sets.

If you're able to run from Custom Reports, Sage will populate the report data in TempDB and generally this alleviates the potential locking and contention issue. But if you have to run from Designer

1.  Verify Use Indexes is checked in File / Report Options
2. Run a SQL Maintenance Plan to rebuild indexes or reorganize indexes if it hasn't been done for awhile (start by just doing this only)
3. Choose Perform Grouping on Server in File / Report Options
4. In Mgmt Studio create a view to join the table you need and add the columns the rpt will need. Now you have a few options:

a.       Index the view. Then change your rpt to use the view instead of separate tables

b.       Create a stored procedure (proc) who's only purpose is to run the view. Then in Crystal go to File / Options / Database and tick Stored Procedures. Then Verify Database and now the proc will appear along with tables and views.

------------------------------
Alnoor Cassim
------------------------------