Problem
sp_StatUpdate assembles a very large dynamic discovery batch (plus separate mop-up discovery batches) and executes them without any way to print the final assembled SQL. When a token substitution or quoting bug produces an invalid statement, the failure is only observable by re-creating the environment and intercepting the string — and per-item TRY/CATCH can mask the syntax error entirely. A quoting defect of exactly this shape survived in the mop-up discovery path for ~3 months undetected.
Fix
- Under
@Debug, print every dynamic batch before execution — in 4,000-char chunks (RAISERROR WITH NOWAIT) so nothing truncates — plus a LEN() line to catch silent nvarchar truncation.
- Maintain a
@current_phase breadcrumb variable updated at each major step, and include it in all CATCH-block error output so failures self-locate.
Pattern source: DarlingData house convention — every dynamic string must be debug-printable before execution.
Problem
sp_StatUpdate assembles a very large dynamic discovery batch (plus separate mop-up discovery batches) and executes them without any way to print the final assembled SQL. When a token substitution or quoting bug produces an invalid statement, the failure is only observable by re-creating the environment and intercepting the string — and per-item TRY/CATCH can mask the syntax error entirely. A quoting defect of exactly this shape survived in the mop-up discovery path for ~3 months undetected.
Fix
@Debug, print every dynamic batch before execution — in 4,000-char chunks (RAISERROR WITH NOWAIT) so nothing truncates — plus aLEN()line to catch silent nvarchar truncation.@current_phasebreadcrumb variable updated at each major step, and include it in all CATCH-block error output so failures self-locate.Pattern source: DarlingData house convention — every dynamic string must be debug-printable before execution.