Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== ====== ===== Error Message ===== Eventually results in a SQL Timeout error message for other users. Also causes reports to hang on "Preparing Reports" ===== Explanation of Error ===== 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. ===== Severity ===== **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. ===== Possible Root Cause(s) ===== 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. ===== Resolution ===== - 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|Blocked Connections in SQL]] - Execute the [[control_sql_-_odbc_process_removal_query]] - Restart SSLIP and Control - Change the SQL options.You can determine if read_committed_snapshot is already set with the following query: [[code_formatsql|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> ===== Alternate Resolution for Control 4.3 - Rebuild Indexes ===== 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.