3

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?

7

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

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.