SQL recently run queries with large amount of text

Question

I have run this query with and without the LEN() on b.text the result that come back I can see the query that I executed but it cuts off at ~ 51k characters what can I do to get the full text of the query?

SELECT
    a.last_execution_time AS ExectuionTime,
    LEN(b.text) AS Query
FROM 
    sys.dm_exec_query_stats a
CROSS APPLY 
    sys.dm_exec_sql_text(a.sql_handle) b 
WHERE 
    b.text LIKE '%FLC%'
    AND a.last_execution_time BETWEEN '2016-04-05 15:00:00' AND '2016-04-05 15:30:00'
 ORDER BY 
     a.last_execution_time DESC

**Edit Thanks to all for your help following the link supplied by Gordon I was able to get to this. The xml contained the entire query. Again thank you all for the input.

SELECT
    CAST('<root><![CDATA[' + b.text + ']]></root>' AS XML)
FROM sys.dm_exec_query_stats a
    CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE 
    b.text like '%FLC%'
    AND a.last_execution_time BETWEEN '2016-04-05 15:00:00' AND '2016-04-05 15:30:00'
ORDER BY a.last_execution_time DESC

This is a problem in the interface. Assign the query to a variable and print it out in chunks: http://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement. — Gordon Linoff, Apr 06 '16 at 16:04
A 50K long query? I suspect you have some serious performance issues — Panagiotis Kanavos, Apr 06 '16 at 16:04

Michael G · Accepted Answer · 2016-04-06T16:21:04.913

2

The max characters you can return form Sql Server Management Studio is 65,535 when in Results To Grid mode.

You have a few options:

You'll need to write out the string in parts.
Write the results to a file inside of the results grid. Right click -> Save Results As.
Cast to XML

CAST(b.text AS XML) -- there is no character limitation for XML.

edited Apr 06 '16 at 16:21

answered Apr 06 '16 at 16:11

Michael G

6,695
2
41
59

SQL recently run queries with large amount of text

1 Answers1