Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_error_-_stuck_on_printing_reports [2019/01/27 11:28]
control_error_-_stuck_on_printing_reports [2019/01/27 11:28] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +===== 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.
 +