Differences
This shows you the differences between two versions of the page.
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 " | ||
+ | |||
+ | |||
+ | |||
+ | ===== 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' | ||
+ | |||
+ | |||
+ | |||
+ | ===== 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' | ||
+ | |||
+ | |||
+ | |||
+ | ===== 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= ' | ||
+ | |||
+ | |||
+ | |||
+ | < | ||
+ | * Open SQL Management Studio 2005 | ||
+ | * Restart the SQL Process which contains the affected StoreData | ||
+ | * Select New Query | ||
+ | * Insert query | ||
+ | [[code format=" | ||
+ | |||
+ | |||
+ | |||
+ | alter database [StoreData] | ||
+ | |||
+ | |||
+ | |||
+ | set read_committed_snapshot on | ||
+ | |||
+ | |||
+ | |||
+ | go | ||
+ | |||
+ | |||
+ | |||
+ | < | ||
+ | ===== Alternate Resolution for Control 4.3 - Rebuild Indexes ===== | ||
+ | Run the following query to rebuild indexes for the entire database | ||
+ | EXEC sp_createstats ' | ||
+ | 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. | ||
+ | | ||