Blog › ICP guides

Crystal Reports developer on retainer: SQL cursor deadlock during concurrent billing runs, SAP Crystal Reports developer, Crystal Reports XI developer on monthly retainer

October 10, 2026 · ~15 min read

A Crystal Reports developer was maintaining a billing report suite for a regional commercial insurance company running Crystal Reports 2016 SP08, embedded in a .NET Framework 4.8 Windows Forms application, connected to SQL Server 2019 on Windows Server 2019. The reports — originally built in Crystal Reports 8.5 for a VB6 application in the early 2000s, migrated to Crystal Reports XI for a .NET 2005 application, and migrated again to Crystal Reports 2016 during a .NET Framework upgrade — generated monthly premium billing statements for the insurer’s commercial property and casualty accounts. The principal billing report, BILLING_MONTHLY.rpt, joined the ORDER_HEADER table (1.2 million rows, policies renewed monthly) and the ORDER_LINES table (8.7 million rows, individual coverage line items per policy) to aggregate premium amounts by coverage type, apply endorsement adjustments, and produce a formatted billing statement. Crystal Reports opened its own database connection in pull model — the Crystal Reports CRDB ADO driver opened an independent ADO connection to SQL Server, separate from the connections held by the billing application’s own ADO.NET connection pool — and the Crystal cursor scanned through ORDER_LINES with a SELECT ORDER_HEADER.*, ORDER_LINES.* FROM ORDER_HEADER INNER JOIN ORDER_LINES ON ORDER_HEADER.ORDER_ID = ORDER_LINES.ORDER_ID WHERE ORDER_HEADER.BILLING_DATE BETWEEN @StartDate AND @EndDate query. At month-end billing, fifteen billing clerks ran the report simultaneously from their Windows Forms application instances. SQL Server’s default READ COMMITTED isolation level meant that Crystal’s cursor held shared locks on each 8KB data page as it read from ORDER_LINES. At the same moment, the policy renewal system was still writing new policy endorsement records into ORDER_LINES for policies renewing in the current billing period. An INSERT into ORDER_LINES by the renewal system (SPID 88) required SQL Server to split the clustered index page (1:48391) to accommodate the new row — the page split needed an exclusive lock on page (1:48391). Crystal’s cursor (SPID 74) was holding a shared lock on the same page (1:48391) while reading the rows on it. SQL Server’s deadlock monitor detected the cycle — SPID 74 held a shared lock on page (1:48391) and was waiting on a lock held by a lock the renewal system held elsewhere; SPID 88 held an intent exclusive lock on the ORDER_LINES table and needed the exclusive lock on page (1:48391) blocked by SPID 74 — and selected SPID 74 (Crystal) as the deadlock victim because Crystal had the lower @@DEADLOCK_PRIORITY. The Crystal preview window in the billing clerk’s application died with a CrystalDecisions.CrystalReports.Engine.DataSourceException containing the SQL Server error message “Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.” Billing run failures per week at month-end: 3–5. The billing clerks learned to retry the report 30 seconds after the failure, which usually succeeded because the page split had completed by then.

The root cause was the interaction between Crystal Reports’ pull-model database connection using SQL Server READ COMMITTED isolation and SQL Server’s page-level index structure. SQL Server READ COMMITTED isolation, the default, acquires shared locks on data pages as the query cursor reads each page. In a short OLTP query that reads a handful of rows, the shared locks are held briefly and released as the cursor advances to the next page. In a Crystal Reports pull-model query that reads hundreds of thousands of rows from a large table during report preview (which can take 30–90 seconds for a billing report joining two large tables), shared locks are held on the current page while the Crystal cursor is processing the rows on that page. During a page split triggered by a concurrent INSERT — a common event when a clustered index table is actively receiving inserts in the middle of the key range being scanned by the Crystal cursor — SQL Server needs an exclusive lock on the page being split to reorganize it. If Crystal holds a shared lock on that exact page at the moment the page split is triggered, SQL Server must choose a deadlock victim. Crystal, as a read-only reporting cursor with lower deadlock priority than the OLTP insert transaction, is the usual victim. The resolution is to change Crystal’s database reads to use READ UNCOMMITTED isolation (via the WITH (NOLOCK) table hint on all tables in the Crystal SQL Command, or by setting the isolation level on the Crystal ADO connection to READ UNCOMMITTED before executing the report query). READ UNCOMMITTED means Crystal reads pages without acquiring shared locks — it can read uncommitted data (dirty reads), but for a billing totals report that is reconciled at day end against committed data, slightly stale or mid-transaction rows are acceptable, and the deadlock is structurally impossible because Crystal never acquires shared locks that block exclusive locks.

The month-end frequency and random occurrence pattern are what make Crystal Reports deadlock bugs hard to reproduce in development. The developer who built BILLING_MONTHLY.rpt tested it against the development SQL Server instance with a single database connection: one Crystal preview, no concurrent OLTP writes to ORDER_LINES during the test. The Crystal cursor scanned ORDER_LINES without incident because no concurrent session was inserting rows into the clustered index range being scanned. In production, the deadlock condition requires two precisely-timed events: a Crystal cursor holding a shared lock on a specific index page, and a concurrent INSERT requiring a page split on that exact page. During normal month-end billing operations with fifteen clerks running reports simultaneously while the policy renewal system is inserting thousands of new endorsement records, the coincidence of these events happens 3–5 times per week. Each occurrence is random in timing, affecting whichever billing clerk’s Crystal cursor happened to be on the specific page being split. The SQL Server Extended Events deadlock graph XML — captured by enabling the system_health session (enabled by default in SQL Server 2019) and reading the deadlock events via SELECT XDR.query('.') FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS Data CROSS APPLY Data.query('/RingBufferTarget/event[@name=''xml_deadlock_report'']') AS XDR — showed the deadlock cycle unambiguously: victim process (Crystal, SELECT ORDER_HEADER.*, ORDER_LINES.*, shared lock on page (1:48391)) and request process (renewal INSERT, exclusive lock request on page (1:48391) blocked by Crystal). The fix: the Crystal SQL Command was changed from a direct table join to a SQL Command using WITH (NOLOCK) hints; the ADO connection string used by the Crystal CRDB driver was updated to include SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the CRDB initialization property. Deadlock frequency after fix: 0.

Crystal Reports in 2026: the embedded report engine powering enterprise .NET, VB6, and Java billing applications

Crystal Reports — originally developed by Crystal Services Inc. in Vancouver, British Columbia in the early 1990s, acquired by Seagate Software in 1994, spun off as Crystal Decisions in 2000, acquired by Business Objects in 2003, and acquired by SAP SE when SAP acquired Business Objects in 2008 — is the dominant embedded report designer and runtime that was bundled with Microsoft Visual Studio from VS 6.0 through VS 2010. Every developer who used Visual Basic 6, Visual Basic .NET 2002/2003/2005, or Visual C# 2002/2003/2005 had Crystal Reports included in their installation, and many chose it as the path-of-least-resistance for adding formatted printable reports to their applications. The result is that Crystal Reports is embedded in a vast inventory of enterprise applications built between 1995 and 2015: insurance billing systems, manufacturing ERP reports, distribution warehouse documents (pick lists, packing slips, bills of lading), hospital patient billing statements, government procurement printing, and utility customer statements. These applications are maintained on Crystal Reports 8.5, Crystal Reports XI, Crystal Reports 2008, Crystal Reports 2013, Crystal Reports 2016, and Crystal Reports 2020 (SAP Crystal Reports), with the newer versions running on SAP Crystal Reports Runtime for Visual Studio 2022 or SAP Crystal Reports for Visual Studio 2019.

Crystal Reports developer scarcity is driven by the same attrition dynamic as all legacy tools: the population of developers who understand Crystal Reports formula evaluation passes (the three-pass model: BeforeReadingRecords, WhileReadingRecords, and WhilePrintingRecords), shared variable scoping between subreports, pull-model vs. push-model datasource architecture, the CRDB driver connection pooling behavior, and the Crystal Reports .NET SDK threading model (ReportDocument is not thread-safe; concurrent requests from an ASP.NET application require one ReportDocument instance per request, not a shared instance) is declining while the installed base of Crystal Reports-dependent applications persists. Crystal Reports is not dead: SAP sells Crystal Reports 2020 and continues shipping Crystal Reports Runtime for Visual Studio 2022. But the developer community writing new Crystal Reports code is small, and organizations that need a specialist to diagnose a deadlock in a Crystal Reports billing suite that has been running since 2001 are finding it harder to locate that expertise than it was a decade ago.

The Crystal Reports .NET threading model is a frequent source of retainer-level work that is underestimated in its severity. In web applications that embed Crystal Reports, each concurrent user request that triggers a Crystal report preview or PDF export must use its own independent ReportDocument instance. If the application creates a single ReportDocument at application startup and shares it across threads (a natural optimization that developers apply to amortize the cost of loading the RPT file), concurrent requests corrupt each other’s report data, parameter values, and database connection state, producing garbled reports, wrong-data reports, and application exceptions that are random in occurrence and extremely difficult to reproduce in single-user development testing. The diagnosis requires correlating the Crystal Reports error — typically an InvalidCastException or NullReferenceException deep in the Crystal Reports runtime stack — with the load pattern: the error only appears when two or more users request the same report within milliseconds of each other. The fix is straightforward (create a new ReportDocument instance per request, dispose it after the response is returned), but identifying the root cause in a legacy ASP.NET WebForms application that shared a single static ReportDocument instance across hundreds of concurrent billing clerk sessions is non-obvious diagnostic work.

Crystal Reports datasource architecture: pull model, push model, SQL Command, and deadlock-safe isolation

Crystal Reports supports two datasource architecture patterns: pull model and push model. In pull model (the default when a report is designed in Crystal Reports Designer by pointing it at a database table or stored procedure), Crystal Reports opens its own independent database connection via its CRDB driver (CRDB_ADO for ADO connections, CRDB_ODBC for ODBC connections, CRDB_DAO for DAO/MDB connections, or native CRDB drivers for specific databases) and executes its own SQL SELECT query against the database at report preview time. The application code sets the ReportDocument.SetDatabaseLogon or ReportDocument.DataSourceConnections[0].ConnectionInfo parameters to specify the database server, credentials, and connection string, and Crystal opens the connection. In pull model, Crystal holds the database connection and cursor open for the entire duration of report rendering — from the moment the report starts executing its query until the report has finished reading all the rows it needs. For a 30-second billing report reading hundreds of thousands of rows from SQL Server with READ COMMITTED isolation, this means shared locks are held on data pages for 30 seconds, creating the page-split deadlock vulnerability described above. In push model, the application code opens its own database connection, executes the query, populates an ADO.NET DataSet or DataTable with the results, disposes the database connection (releasing all locks), and then calls ReportDocument.SetDataSource(dataSet) to inject the in-memory data into Crystal for rendering. In push model, Crystal never opens a database connection and never holds any database locks — the lock exposure is limited to the duration of the application’s own query execution, which is typically much shorter than the Crystal pull-model rendering duration and is controlled by the application’s own connection management.

The SQL Command datasource in Crystal Reports Designer allows the developer to replace Crystal’s auto-generated SELECT with a custom SQL statement. This is the correct mechanism for adding WITH (NOLOCK) hints to Crystal’s database reads in pull model without migrating to push model. To create a SQL Command datasource: in Crystal Reports Designer, open Database Expert, expand the connection under the Available Data Sources tree, navigate to “Add Command”, and enter the custom SQL including the WITH (NOLOCK) table hints. The SQL Command replaces Crystal’s auto-generated join query with the developer-specified query. For the billing report deadlock fix, the SQL Command was: SELECT o.ORDER_ID, o.ACCOUNT_ID, o.BILLING_DATE, o.POLICY_NUMBER, o.POLICY_TYPE, o.BILLING_PERIOD_START, o.BILLING_PERIOD_END, l.LINE_ITEM_ID, l.COVERAGE_TYPE, l.BASE_PREMIUM, l.ENDORSEMENT_ADJUSTMENT, l.FINAL_PREMIUM FROM ORDER_HEADER o WITH (NOLOCK) INNER JOIN ORDER_LINES l WITH (NOLOCK) ON o.ORDER_ID = l.ORDER_ID WHERE o.BILLING_DATE BETWEEN {?StartDate} AND {?EndDate} ORDER BY o.ACCOUNT_ID, o.ORDER_ID, l.COVERAGE_TYPE. The WITH (NOLOCK) hint instructs SQL Server to use READ UNCOMMITTED isolation for those table reads: Crystal’s cursor reads the pages without acquiring shared locks, making it impossible for a concurrent page split to deadlock against Crystal’s locks. The trade-off — Crystal may read uncommitted data (rows that a concurrent transaction has written but not yet committed, which might later be rolled back) — is acceptable for a billing totals report that is reconciled against committed data at day end. For reports where accuracy of every individual transaction record is critical (audit reports, regulatory reports), push model with a DataSet queried in a separate transaction is the safer approach because the application can query with READ COMMITTED SNAPSHOT isolation on SQL Server 2019 and get consistent reads without shared lock contention.

Typical Crystal Reports developer retainer work and what it looks like in a work log

SQL Server deadlock during Crystal pull-model billing run is the canonical Crystal Reports invisible production concurrency bug for any report that scans large tables using pull model while concurrent OLTP writes are occurring. It appears in the work logs of Crystal Reports retainer engagements for insurance billing systems, manufacturing invoice printing, distribution warehouse document generation, and hospital patient billing that run Crystal pull-model reports in multi-user environments where the same tables are simultaneously receiving OLTP writes during report preview. The work log entry that makes this diagnosable: “Crystal Reports 2016 SP08 (.NET Framework 4.8 WinForms); SQL Server 2019 (15.0.4355); report BILLING_MONTHLY.rpt; tables ORDER_HEADER (1.2M rows) and ORDER_LINES (8.7M rows); pull model (CRDB_ADO); deadlock graph XML from sys.fn_xe_file_target_read_file (system_health XEL): victim SPID 74 (Crystal SELECT, shared lock on page (1:48391)); request SPID 88 (INSERT INTO ORDER_LINES, exclusive lock on page (1:48391) for index IX_ORDER_LINES_ORDER_ID page split); deadlock frequency before fix: 3–5 per week at month-end; fix: SQL Command datasource added with WITH (NOLOCK) on ORDER_HEADER and ORDER_LINES; CRDB connection initialization string updated with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; deadlock frequency after fix: 0; hours: 2h diagnosis + 1.5h fix + 1h regression testing = 4.5h.”

Push model DataSet injection migration is the highest-quality long-term fix for Crystal Reports pull-model lock issues and the most common planned Crystal Reports retainer engagement for organizations that want to eliminate all Crystal database lock exposure rather than just converting to NOLOCK. The migration converts Crystal reports from pull model (Crystal opens its own connection) to push model (application queries data, disposes connection, injects DataSet into Crystal). A typical engagement for the insurance billing system: the retainer developer creates a DataAccess class that queries ORDER_HEADER and ORDER_LINES using a SqlDataAdapter, fills a typed DataSet (BillingReportDS) with ORDER_HEADER_TABLE and ORDER_LINES_TABLE DataTables, calls SqlConnection.Close() to release the connection and all SQL Server locks before Crystal starts rendering, then calls ReportDocument.SetDataSource(billingReportDS) to inject the in-memory DataSet. The Crystal report RPT file is updated to use the DataSet schema as the datasource (the DataSet field names must match the Crystal field names used in the report formulas and sections). Work log: “BILLING_MONTHLY.rpt push model migration; Crystal Reports 2016 SP08; SQL Server 2019; created BillingReportDS typed DataSet (ORDER_HEADER_TABLE: 12 columns; ORDER_LINES_TABLE: 8 columns with ORDER_ID foreign key); SqlDataAdapter query: SELECT with READ COMMITTED SNAPSHOT isolation (RCSI enabled on BILLING_DB); connection closed before SetDataSource; ReportDocument.SetDataSource(billingReportDS); RPT datasource updated to BillingReportDS schema; tested with 14-month production data extract (1.2M header rows, 8.7M line rows); rendering time: 28s (vs. 34s pull model); 0 deadlocks (connection closed before Crystal renders); 0 dirty reads (RCSI provides consistent snapshot); report results validated against prior month billing totals; hours: 8h.”

Crystal Reports subreport performance optimization is the most common Crystal Reports retainer engagement that clients underestimate in complexity. A Crystal report with embedded subreports — for example, a policy summary report with a subreport for each coverage endorsement detail — fires one independent database query per subreport instance per report row in pull model. A billing report with 500 policy rows and one subreport per policy fires 500 subreport queries against the database, each opening and closing an independent Crystal database cursor. For a database with 1.2 million policy rows, this produces thousands of short queries instead of one large query, creating query-per-row N+1 problems that dramatically slow report generation and create connection pool exhaustion under concurrent users. The fix: restructure the subreport as an on-demand subreport with shared variables, or migrate the subreport logic into the main report using a GROUP-BY with a detail section suppression, or migrate to push model and pre-join the data in the DataSet before injection. Work log: “POLICY_SUMMARY.rpt subreport optimization; Crystal Reports 2016; SQL Server 2019; subreport ENDORSEMENT_DETAIL.rpt fires 1 query per policy row in group footer; 500 policy rows × 1 subreport query = 500 SQL Server round-trips; query execution: 18s (vs. target <5s); fix: subreport migrated to main report — ENDORSEMENT_DETAIL data added to BillingReportDS as ENDORSEMENT_TABLE DataTable, linked on ORDER_ID; detail section conditional suppression handles rows with no endorsements; report rendering after fix: 4.1s; hours: 5h.”

How HourTab tracks Crystal Reports developer retainer hours

Crystal Reports deadlock bugs caused by pull-model shared lock contention with concurrent OLTP writes are invisible in development testing by the same mechanism that makes all multi-user lock bugs invisible when only one session accesses the data at a time. The developer who builds BILLING_MONTHLY.rpt tests it against the development SQL Server instance with a single database connection, no concurrent policy renewal inserts running. The Crystal cursor scans ORDER_LINES without contention because no concurrent session is triggering index page splits during the scan. The deadlock requires the precise coincidence of a Crystal cursor holding a shared lock on a specific 8KB page and a concurrent INSERT requiring an exclusive lock on the same page at the same millisecond. This coincidence is statistically rare with one Crystal session and no concurrent writes, and statistically inevitable with fifteen billing clerks running reports simultaneously while the renewal system inserts thousands of new endorsement records during the last days of the billing month.

The work log entry that makes Crystal Reports lock work auditable must name every element of the causal chain: the Crystal Reports version and edition (Crystal Reports 2016 SP08 vs. Crystal Reports for Visual Studio 2022 bundled runtime — different CRDB driver versions with different default connection behaviors), the report file name, the database platform and version (SQL Server 2019 vs. Oracle 19c — Oracle does not use page-level shared locks for read cursors; the deadlock pattern is SQL Server-specific), the tables involved and their row counts (ORDER_LINES at 8.7M rows means the Crystal cursor holds shared locks for 30+ seconds, making page-split collision statistically likely), the deadlock graph XML evidence (victim SPID, request SPID, lock type and page number), and the before/after deadlock frequency. A log that says “fixed Crystal Reports lock issue, 4.5h” is not auditable to an insurance billing manager who needs to explain to their CFO why five billing runs failed at month-end. A log that names the report, the deadlock graph evidence, the NOLOCK fix, and the zero-deadlock outcome after the fix is auditable and demonstrates the value of a Crystal Reports retainer relationship for an insurer whose monthly billing cycle depends on the Crystal reporting application. HourTab gives Crystal Reports developers a public retainer-hours URL they send to clients — insurance companies, manufacturers, distributors, hospitals, and utilities that run critical billing and operational reporting on Crystal Reports-embedded .NET applications maintained by a Crystal Reports specialist on monthly retainer.

The broader context for Crystal Reports retainer billing is that the Crystal pull-model deadlock is the Crystal Reports instantiation of the universal principle that long-running read cursors holding shared locks on actively-written tables create inevitable lock contention. Oracle Forms developer retainers cover the equivalent in Oracle Forms: SELECT ... FOR UPDATE acquiring an Oracle row lock in LOCKING_MODE AUTOMATIC that is held for the entire operator session while the operator is interrupted, blocking concurrent Oracle sessions on the same row. VBA developer retainers cover the equivalent in Access VBA: a DAO or ADO Recordset opened in pessimistic lock mode that holds a JET engine row or page lock while the VBA procedure iterates through records, preventing other VBA procedures in other Access sessions from accessing the locked rows. Progress OpenEdge developer retainers cover the equivalent in Progress ABL: FIND FIRST EXCLUSIVE-LOCK acquiring a Progress record lock held for the duration of the procedure block, with RELEASE missing on the failure path that exits the block without committing or explicitly releasing the lock.

FAQ: Crystal Reports developer retainers