Msg 105 Unclosed quotation mark after the character string '(data[@name="is_recompi'.
gflpcantor31 opened this issue · 6 comments
First of all, I'd like to thank you for your work, which I imagine is intense and requires a lot of effort. Many thanks. Your work is of public utility
Version of the script
dbo.sp_HumanEvents
version : '5.0',
version_date : '20230801';
What is the current behavior?
I regularly get the following error:
Msg 105, Level 15, State 1, Line 67
Unclosed quotation mark after the character string '(data[@name="is_recompi'.
According to my investigations, the problem lies in the following SQL statement :
The problem stems from the fact that the content of the @table_sql variable is systematically truncated at 4000 characters, which explains the origin of the error, since, in reality, the query text exceeds 4000 characters.
INSERT INTO [My_Database].[dbo].keeper_HumanEvents_compiles WITH(TABLOCK)
( server_name, event_time, event_type,
database_name, object_name, statement_text , compile_cpu_ms, compile
.....
.....
.....
oa.c.value('xs:hexBinary((data[@name="statement_sql_hash"]/value/text())[1])', 'varbinary(64)') AS statement_sql_hash
FROM #human_events_xml_internal AS xet
OUTER APPLY xet.human_events_xml.nodes('//event') AS oa(c)
WHERE oa.c.exist('@name[.= "query_parameterization_data"]') = 1
AND oa.c.exist('(data[@name="is_recompi
If the current behavior is a bug, please provide the steps to reproduce.
EXEC sp_HumanEvents
@event_type = N'compiles',
@keep_alive = 1;
EXEC sp_HumanEvents
@event_type = N'recompiles',
@keep_alive = 1;
EXEC sp_HumanEvents
@event_type = N'query',
@keep_alive = 1;
EXEC sp_HumanEvents
@event_type = N'waits',
@keep_alive = 1;
EXEC sp_HumanEvents
@event_type = N'blocking',
@keep_alive = 1;
EXEC sp_HumanEvents
@output_database_name = N'My_Database',
@output_schema_name = N'dbo';
What is the expected behavior?
Make sure that when the query text is too long, exceeding 4000 characters, it's not truncated and therefore doesn't generate an error.
Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Microsoft SQL Server 2019 (RTM-CU18-GDR) (KB5021124) - 15.0.4280.7 (X64)
Jan 23 2023 12:37:13
Copyright (C) 2019 Microsoft Corporation
Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: ) (Hypervisor)
@gflpcantor31 thanks for reporting this. It didn't come up in any of my testing.
It's sort of an unfortunate state of things with dynamic SQL where at different times in different places string concatenation sometimes implicitly converts to lower byte lengths, causing these truncation errors. There's no great workaround for it other than to add a convert to nvarchar max around every string fragment, which is pretty tedious.
In the meantime, if you find which concatenation point in the dynamic SQL causes this, please let me know.
@gflpcantor31 can you try the version in the dev branch and let me know if that resolves the error for you?
Hello @erikdarlingdata
I've just tested the latest version, I'm sorry, but the error still occurs identically.
Hello @erikdarlingdata
To correct the error, I had to make the following two types of changes:
1 - Because of data type precedence (1) , I added the following:
@table_sql = CAST(N'' AS nvarchar(max)) +
....
....
2 - Again because of data type precedence and doubts about the data types returned by CASE expressions (?), as a precaution I applied a CAST to CASE expressions like this:
CAST ( CASE
WHEN ….
WHEN …
ELSE …. END AS nvarchar(max))
(1) Data type precedence https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-ver16
(2)
sp_HumanEvents__#273_gflpCantor31.zip
Maybe I've missed a few, but the only type 1/ change I made didn't resolve the error. That's why, to remove any doubts, I applied type 2/ modifications
Attached you'll find the script sp_HumanEvents__#273_gflpCantor31.zip where I've applied these modifications. With these modifications, I no longer reproduce the error. I'll leave it up to you to check and validate these modifications.
@gflpcantor31 If you open a proper pull request with these changes, I'm happy to take a look. It's a bit much to ask me to scroll through 3000 lines looking for your changes and then copying them over into my local file. Thanks!
Bonjour @erikdarlingdata
As desired, I've opened a pull request incorporating the changes.
I went back to your last CONVERT modifications (nvarchar(max), ..) and replaced them with a CAST, but only on the CASE expressions that caused the problem.
I've run a few tests. As far as I'm concerned, everything's OK and I don't see the error anymore.
I hope I haven't forgotten anything. I'm not very familiar with Git and Github procedures :)