4

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.

5

This can be due to the maximum amount of characters that the result to grid can return, 65535.

enter image description here

Starting from SSMS 18.2 you are able to change this.

Thanks Aaron Bertrand for pointing that out.

Aaron also mentions:

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).

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

enter image description here

Thanks Erik Darling for linking some more related answers

  • 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 Aug 17 at 10:43
  • @EvgeniyGribkov Great, my bad. You could add it as a separate answer or Ill add it to this answer, your choice :). Thanks – Randi Vertongen Aug 17 at 11:05
  • 1
    You 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 Aug 17 at 12:40
  • There are some related answers here: dba.stackexchange.com/questions/205720/… – Erik Darling Aug 17 at 13:47
  • It's also the last time @AaronBertrand was funny on the internet. – Erik Darling Aug 17 at 13:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.