How can the Profiler textdata field be enlarged?

[Robert]

When running Profiler to save a trace to a table the field 'TextData' gets
trunciated. The datatype for that field is as shown:

Column name | Data Type | Length | Allow Null

TextData ntext 16 yes


I can not find a way to set the trace up to create a tablewith TextData of
length greater than 16. This really causes a problem when trying to capture
long running queries for tuning as the query itself maybe truncated. Is
there a way around this?

[Simon Hayes]

Re: How can the Profiler textdata field be enlarged?


"Robert" <stop.spam@boei ng.com> wrote in message
news:HtAJF7.7yM @news.boeing.co m...[color=blue]
> When running Profiler to save a trace to a table the field 'TextData' gets
> trunciated. The datatype for that field is as shown:
>
> Column name | Data Type | Length | Allow Null
>
> TextData ntext 16 yes
>
>
> I can not find a way to set the trace up to create a tablewith TextData of
> length greater than 16. This really causes a problem when trying to[/color]
capture[color=blue]
> long running queries for tuning as the query itself maybe truncated. Is
> there a way around this?
>
>[/color]
ntext can store up to 1GB of Unicode text, so you should be able to see the
full query text. If not, perhaps you can clarify how you are viewing the
data? Using Query Analyzer, or another tool? QA displays only 256 characters
by default, so you may need to check your settings (Tools - Options -
Results).

By default, MSSQL stores a pointer to ntext data in the table, not the data
itself, which is where the 16 bytes comes from - that is the size of the
pointer only, not the data.

Simon

[Robert]

Re: How can the Profiler textdata field be enlarged?


"Simon Hayes" <sql@hayes.ch > wrote in message
news:4033b59f$1 _3@news.bluewin .ch...[color=blue]
>
> "Robert" <stop.spam@boei ng.com> wrote in message
> news:HtAJF7.7yM @news.boeing.co m...[color=green]
> > When running Profiler to save a trace to a table the field 'TextData'[/color][/color]
gets[color=blue][color=green]
> > trunciated. The datatype for that field is as shown:
> >
> > Column name | Data Type | Length | Allow Null
> >
> > TextData ntext 16 yes
> >
> >
> > I can not find a way to set the trace up to create a tablewith TextData[/color][/color]
of[color=blue][color=green]
> > length greater than 16. This really causes a problem when trying to[/color]
> capture[color=green]
> > long running queries for tuning as the query itself maybe truncated. Is
> > there a way around this?
> >
> >[/color]
> ntext can store up to 1GB of Unicode text, so you should be able to see[/color]
the[color=blue]
> full query text. If not, perhaps you can clarify how you are viewing the
> data? Using Query Analyzer, or another tool? QA displays only 256[/color]
characters[color=blue]
> by default, so you may need to check your settings (Tools - Options -
> Results).
>
> By default, MSSQL stores a pointer to ntext data in the table, not the[/color]
data[color=blue]
> itself, which is where the 16 bytes comes from - that is the size of the
> pointer only, not the data.
>
> Simon
>[/color]

Thanx Simon

You nailed it! I am not worthy.