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
TopAnswers
Databases
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