sp_QuickieStore: Expert Mode Always Fails (compilation_stats, SWITCHOFFSET) #535
Description
Version of the script
Debug mode says the version is 5.4 and version_date is 2025-04-01 00:00:00.000. In truth, I pulled it straight from main.
What is the current behavior?
Run EXEC sp_QuickieStore @expert_mode = 1. I error like this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
x.*
FROM
(
SELECT
source =
'compilation_stats',
database_name =
DB_NAME(qsq.database_id),
qsq.query_id,
qsq.object_name,
qsq.query_text_id,
qsq.query_parameterization_type_desc,
initial_compile_start_time =
CASE
WHEN @timezone IS NULL
THEN
SWITCHOFFSET
(
qsq.initial_compile_start_time,
@utc_offset_string
)
WHEN @timezone IS NOT NULL
THEN qsq.initial_compile_start_time AT TIME ZONE @timezone
END,
initial_compile_start_time_utc =
qsq.initial_compile_start_time,
last_compile_start_time =
CASE
WHEN @timezone IS NULL
THEN
SWITCHOFFSET
(
qsq.last_compile_start_time,
@utc_offset_string
)
WHEN @timezone IS NOT NULL
THEN qsq.last_compile_start_time AT TIME ZONE @timezone
END,
last_compile_start_time_utc =
qsq.last_compile_start_time,
last_execution_time =
CASE
WHEN @timezone IS NULL
THEN
...
Msg 9812, Level 16, State 0, Line 3
The timezone provided to builtin function switchoffset is invalid.Debug mode reports the same as what it always has for the relevant variable values, so the error is clearly in either dynamic SQL (which I don't think has changed) or how the SQL is called. I've noticed that we're passing in has changed from nvarchar(6) to nvarchar(max). I have no idea how sys.sp_executesql knows the difference, but changing it back fixed the problem on my machine.
If the current behavior is a bug, please provide the steps to reproduce.
As above.
What is the expected behavior?
No error.
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Presumably all, but I happen to be on a docker container with a +10:00 time zone.
IMPORTANT: If you're going to contribute code, please read the contributing guide first.
https://github.com/erikdarlingdata/DarlingData/blob/main/CONTRIBUTING.md
Yep.