Sql-server – Howto avoid textdata of trace gets truncated

sql serversql-server-2008-r2ssmstrace

I have a trace file including the TEXTDATA column created by a server-side tracing.
Some of the traced queries are very long.

If I re-open the trace file on Profiler, the relevant long query is displayed completely – it has 340 rows of text and 10951 characters.

But after I have imported the trace file into a SQL-Server table, the relevant query seems to be truncated. The table has a ntext column that had been created by the fn_trace_gettable method.

I queried the table using different methods:
The text output in SSMS (with maximized number of characters configured) interrupts the output at line 52.
The file output of SSMS (*.rpt) also truncates, here the query is getting truncated at line 250.

So right now I wonder if it's possible to get the WHOLE query out of the trace file into a SQL-Server table and how?

Best Answer

SSMS always truncates long strings unless typed as XML in which case you can set it to allow unlimited.

enter image description here

The workaround I usually use (from Adam Machanic's workaround to a connect item (internet archive link)) is below

SELECT (SELECT YourTraceDataColumn AS [processing-instruction(x)] FOR XML PATH(''), TYPE)
FROM YourTraceTable

This adds a few leading (<?x) and trailing (?>) characters but otherwise leaves the data intact and without characters being replaced by XML entities.

Related Question