Eventually results in a SQL Timeout error message for other users.

Also causes reports to hang on “Preparing Reports”

When running a Crystal Report with a direct SQL Command, Crystal by default locks the SQL result sets. This means that while someone is viewing a report (has it on the screen) other users may be prevented from opening, saving, etc. their records.

Every report that is run in Control must have the WITH(NOLOCK) clause after each table name that is accessed in the SQL Command to prevent this problem. This mostly occurs with custom reports in which a particular portion of the SQL that makes up the report is missing the WITH(NOLOCK) clause. This doesn't affect the report itself, but causes random lock-ups (usually resulting in a SQL Timeout error) for other reports or others users.

High. The main difficulty about this error is that the user affected is normally not the user causing the problem. Hence, it is very difficulty to trace and very frustrating.

This problem is normally caused by a report that doesn't contain the WITH(NOLOCK) clause after each table accessed in the SQL Command in a Crystal Report, causing the SQL database to remain in a locked state while the report is being printed or previewed.

1. Fix the offending report.
   • To identify the offending report, open the Activity Manager in SQL Server Studio Manager and look for blocked or blocking connections. See Blocked Connections in SQL
2. Execute the control_sql_-_odbc_process_removal_query
3. Restart SSLIP and Control
4. Change the SQL options.You can determine if read_committed_snapshot is already set with the following query:

code format=sql

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'StoreData'

<code>

• Open SQL Management Studio 2005
• Restart the SQL Process which contains the affected StoreData
• Select New Query
• Insert query

code format="sql"

    alter database [StoreData]

    set read_committed_snapshot on

    go

<code>

Run the following query to rebuild indexes for the entire database EXEC sp_createstats 'indexonly', 'fullscan' and then run this query once the above query has completed EXEC sp_RebuildIndexes The query will take a few minutes to run. Once completed, login in Control and try previewing the report again.