Error While Executing dbo.sp_QuickieStore Stored Procedure with @expert_mode = 1

[ravirajch]

Which script is affected?

sp_HealthParser

Script Version

6.5

SQL Server Version

Microsoft SQL Azure (RTM) - 12.0.2000.8 Mar 13 2026 08:25:28 Copyright (C) 2025 Microsoft Corporation

Describe the Bug

While Executing dbo.sp_QuickieStore Stored Procedure with @expert_mode = 1 getting below error

-- Expert mode for additional details
EXECUTE dbo.sp_QuickieStore
@expert_mode = 1;

Msg 8114, Level 16, State 5, Line 38
Error converting data type nvarchar to bigint.

Steps to Reproduce

connect to ssms
select azure database
run below procedure from query analyzer

EXECUTE dbo.sp_QuickieStore
@expert_mode = 1;

Msg 8114, Level 16, State 5, Line 38
Error converting data type nvarchar to bigint.

Expected Behavior

procedure has to execute without error

Actual Behavior

Returning error

Msg 8114, Level 16, State 5, Line 38
Error converting data type nvarchar to bigint.

Error Messages

Msg 8114, Level 16, State 5, Line 38
Error converting data type nvarchar to bigint.

Additional Context

No response

[erikdarlingdata]

Can't reproduce on SQL Server 2022 with @expert_mode = 1. Azure SQL DB exposes some Query Store / DMV columns with different types than the box product, so this is likely Azure-specific.

To pinpoint the failing block, please re-run with debug on and paste:

EXEC dbo.sp_QuickieStore
    @expert_mode = 1,
    @debug = 1;

1. The last current_table value printed before the 8114 — that names the failing section.
2. The dynamic SQL block printed just before the failure.

[erikdarlingdata]

Update: spun up a fresh Azure SQL DB on the same build (12.0.2000.8, Mar 13 2026) at Standard S0, seeded a real workload (114 queries / 113 runtime-stat rows / 233 wait-stat rows), forced a plan, and applied a Query Store hint so every @expert_mode section had data — EXEC dbo.sp_QuickieStore @expert_mode = 1; ran clean, no 8114.

So the conversion failure looks data- or tier-specific. The fastest path to isolating it is the @debug = 1 output:

EXEC dbo.sp_QuickieStore
    @expert_mode = 1,
    @debug = 1;

1. The last current_table value printed before the 8114 — that names the failing section.
2. The dynamic SQL block printed just before the failure.

Also, your service tier would help — different tiers expose Query Store/DMV columns slightly differently:

SELECT
    DATABASEPROPERTYEX(DB_NAME(), 'ServiceObjective') AS service_objective,
    DATABASEPROPERTYEX(DB_NAME(), 'Edition') AS edition;

[ravirajch]

Thank you for the quick response and for validating this on a fresh Azure SQL DB.

I executed the procedure with the @debug = 1 parameter as suggested, and it completed successfully without the conversion error.

EXEC dbo.sp_QuickieStore
@expert_mode = 1,
@debug = 1;

Please let me know if you would still like me to capture and share the debug output, including the last current_table value and the dynamic SQL block, for further review.

Also, I will gather and share the service tier details using the query you provided.

service_objective edition
ElasticPool Hyperscale

[erikdarlingdata]

The run didn't actually succeed — @debug = 1 makes the CATCH block swallow the error and GOTO DEBUG; instead of re-throwing (sp_QuickieStore.sql:14040-14047). The 8114 still happened, you just didn't get a red error. Before the GOTO, the CATCH emits these via RAISERROR ... WITH NOWAIT to the Messages tab:

• current dynamic activity
• error while <something> with @expert mode = 1 and format_output = 0
• current dynamic sql:
• the full failing dynamic SQL

They're at severity 10 (informational) so they show up plain, not red. The <something> is the failing section name (the @current_table value); the dynamic SQL block right after it is the exact statement that breaks. Pasting both pins the bug.

If you'd like the failure to surface as a red error and keep the debug dump in the same run, you can bump those CATCH-block RAISERROR calls from severity 10 to 16 (sp_QuickieStore.sql:14027-14028 and 14036-14037), e.g.:

RAISERROR('error while %s with @expert mode = %i and format_output = %i', 16, 1, @current_table, @em, @fo) WITH NOWAIT;

Severity 16 inside a CATCH is non-terminating, so the existing GOTO DEBUG; still fires and you still get the parameter / temp-table dump — but the client now sees a real error.

[ClaudioESSilva]

I came across this error today.

The path where it fails

current dynamic activity
error while inserting #tuning_recommendations with @expert mode = 1 and format_output = 1

The problem

The parse of the plan_force_json.detail JSON, won't work. It will extract a string which then can't be inserted into the temp table that expects a bigint.

This is the output of the sys.dm_db_tuning_recommendations

Suggested fix

Like it's done on some other places (Example: https://github.com/erikdarlingdata/DarlingData/blob/main/sp_QuickieStore/sp_QuickieStore.sql#L11769) do the same but here

DarlingData/sp_QuickieStore/sp_QuickieStore.sql

Line 11130 in d1e881d

WHERE plan_force_flat.regressed_plan_id = qsp.plan_id

Instead, make it

WHERE TRY_CAST(plan_force_flat.regressed_plan_id AS bigint) = qsp.plan_id

Will submit the suggested fix.

[erikdarlingdata]

@ClaudioESSilva my hero!

[erikdarlingdata]

Hi @ravirajch — PR #771 from @ClaudioESSilva has been merged into dev and should resolve the conversion error you hit.

@ClaudioESSilva — while testing the fix locally I spotted something else in the same parsing block I'd like a second pair of eyes on. In sp_QuickieStore.sql around lines 11091 and 11104, the inner length argument inside the IIF is LEN(',') (which is 1) where it looks like it should be LEN('regressedPlanId: ') / LEN('recommendedPlanId: '). Depending on the actual shape of tr.details on Azure, that could off-by-one the SUBSTRING and silently truncate a digit (e.g. parse 12345 as 1234). Since you've got a working repro tenant, could you paste a row or two of:

SELECT TOP (5) tr.details
FROM sys.dm_db_tuning_recommendations AS tr;

so we can see what you're actually parsing? If your IDs are coming through correct on the merged fix this is probably a non-issue and we'll move on. Appreciate the patch either way.

[ClaudioESSilva]

Hey @erikdarlingdata ,

Here is an example:

{"planForceDetails":{"queryId":234280,"queryHash":"0x8D7059602A","regressedPlanId":9940,"regressedPlanHash":"0x285E49EAE9A",
"regressedPlanExecutionCount":40,"regressedPlanErrorCount":0,"regressedPlanCpuTimeAverage":9.9195001e+002,
"regressedPlanCpuTimeStddev":8.1610979e+002,"recommendedPlanId":9988,"recommendedPlanHash":"0x612F5DE7EB4",
"recommendedPlanExecutionCount":16144,"recommendedPlanErrorCount":0,"recommendedPlanCpuTimeAverage":7.799405333e+001,
"recommendedPlanCpuTimeStddev":6.738825e+001},
"implementationDetails":{"method":"TSql","script":"exec sp_query_store_force_plan @query_id = 234380, @plan_id = 99688"}}

no truncation is happening.
However, a leading space is output.
So the 12345 is actually 12345

To fix this detail, we need to remove the spaces in the JSON to text conversion and in the SUBSTRING all the 'regressedPlanId: ' by 'regressedPlanId:'` it fixes it.

Do you remember why you replaced ':' by : (with a space)?

[erikdarlingdata]

@ClaudioESSilva This was contributed by @ReeceGoding in #691 - I don't think I touched this much.

[ClaudioESSilva]

I see. As it is, it won't break (as tested and proved yesterday). On the insert into the temp table, the text is converted to bigint, and the space disappears.

I think we can leave it like this for now, or do you want to kill that (invisible) space? 😄