Get the full text of a long request2i na 891 tl2 UunZ27l07Ii2o 527Q m5g
How to get the full text of the request from the following request:
SELECT t.[text]
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
Here, with a sufficiently long request, the request text breaks off. I use SSMS 17 and 18 versions.
1 Answer
This can be due to the maximum amount of characters that the result to grid can return, 65535.
You cannot change this to be higher than this number.
The text datatype of sys.dm_exec_sql_text is nvarchar(max), no issues there.
You could cast the column to XML as a workaround
SELECT CAST(t.[text] AS XML)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
Or a better method by Evgeniy Gribkov
SELECT t.[text]
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
FOR XML RAW, ELEMENTS;
Better this way: SELECT t.[text] FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t FOR XML RAW, ELEMENTS; Since the request text can not always be converted to XML. In particular, it cannot convert query text when remotely calling stored procedures: "XML parsing: line 13, character 129, illegal qualified name character"
Or save the results to a file
-
Better this way:
SELECT t.[text] FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t FOR XML RAW, ELEMENTS;Since the request text can not always be converted to XML. In particular, it cannot convert query text when remotely calling stored procedures: "XML parsing: line 13, character 129, illegal qualified name character" – Evgeniy Gribkov 9 hours ago -
@EvgeniyGribkov Great, my bad. You could add it as a separate answer or Ill add it to this answer, your choice :). Thanks – Randi Vertongen 8 hours ago
-
1You can in SSMS 18.2, however if 64kb is the limit you’re hitting, there are a lot of places where statement text is truncated long before that any way (see literally any execution plan). – Aaron Bertrand♦ 7 hours ago
-
There are some related answers here: dba.stackexchange.com/questions/205720/… – Erik Darling 6 hours ago
-
It's also the last time @AaronBertrand was funny on the internet. – Erik Darling 6 hours ago