SEO

#1 (**permalink**) 02-28-2008, 08:03 PM

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?

#2 (**permalink**) 02-28-2008, 08:03 PM

"Robert" <stop.spam@boeing.com> wrote in message
news:HtAJF7.7yM@news.boeing.com...
> 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?
>
>
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

#3 (**permalink**) 02-28-2008, 08:03 PM

"Simon Hayes" <sql@hayes.ch> wrote in message
news:4033b59f$1_3@news.bluewin.ch...
>
> "Robert" <stop.spam@boeing.com> wrote in message
> news:HtAJF7.7yM@news.boeing.com...
> > 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?
> >
> >
> 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
>

Thanx Simon

You nailed it! I am not worthy.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

How can the Profiler textdata field be enlarged?