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][1] 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?

  [1]: https://msdn.microsoft.com/de-de/library/ms188425(v=sql.120).aspx
Top Answer
Martin Smith (imported from SE)
SSMS always truncates long strings unless typed as XML in which case you can set it to allow unlimited.

[![enter image description here][1]][1]

The workaround I usually use (from Adam Machanic's workaround to [a connect item (internet archive link)][2]) 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.

  [1]: https://i.stack.imgur.com/ngr30.png
  [2]: https://web.archive.org/web/20160507221103/https://connect.microsoft.com/SQLServer/feedback/details/499618/ssms-allow-large-text-to-be-displayed-in-as-a-link

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.