Profiler

Where can I change the length or data type (preferrably to nvarchar 4000) of
the textdata field in profiler? My intent is to capture as much of a stored
proc script as possible & save as a trace table, but the TextData column type
of ntext (16) is not sufficient. Or is there a way to capture the body of
text in the lower pane?
JDArsenault wrote:
> Where can I change the length or data type (preferrably to nvarchar
> 4000) of the textdata field in profiler? My intent is to capture as
> much of a stored proc script as possible & save as a trace table, but
> the TextData column type of ntext (16) is not sufficient. Or is there
> a way to capture the body of text in the lower pane?
NTEXT(16) indicates storage for the text pointer. It is always 16 bytes
in length. it's possible the Profiler UI has limitations on the amount
of text it displays. If you use a server-side trace, you will see
everything.
David Gugick
Imceda Software
www.imceda.com
|||Can you briefly describe a server side trace & what I need to do?
"David Gugick" wrote:

> JDArsenault wrote:
> NTEXT(16) indicates storage for the text pointer. It is always 16 bytes
> in length. it's possible the Profiler UI has limitations on the amount
> of text it displays. If you use a server-side trace, you will see
> everything.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||JDArsenault wrote:
> Can you briefly describe a server side trace & what I need to do?
Profiler is just a GUI for the SQL Trace API, with some major
disadvantages. Profiler uses traces that produce rowsets so the UI can
display those results to the user in a streaming fashion. It's a good
option for seeing a short amount of activity on a server. Profiler is
not intended for use, nor is it recommended for use, in an enterprise
fashion because of the increased overhead and no guarantee of seeing all
results.
A server-side trace is a programmatic method to create, start, stop, and
manage traces. The API (accessed through T-SQL) is somewhat verbose, but
does provide a guarantee of seeing all traced information and causes
much less overhead on the server.
You use the sp_trace_* system procs and fn_trace_* system functions to
do this.
The easiest way to see the code for server-side trace is to set one up
using Profiler. Select the options to save the results to a file and
have the server manage the trace. Select the events you want and columns
you need to see. Start the trace. Stop it. Select the File - Script
Trace menu option and Profiler generates much of the code for you.
You can then modify the code as you see fit. You'll also need to
manually stop the trace, so getting the trace handle when the trace
starts is important. You sp_trace_setstatus to start/stop traces.
You always want to trace to a flat file on one of the server's local
drives. Never to a network share or a NAS device. The tempdb/log device
is usually a good place (RAID 5 arrays are not ideal for traces).
When the trace is stopped, you can query the information from the file
using fn_trace_gettable(), import the information into a table using
that function, or open the trace file in Profiler.
David Gugick
Imceda Software
www.imceda.com
|||Thanks for the straight-forward help...
"David Gugick" wrote:

> JDArsenault wrote:
> Profiler is just a GUI for the SQL Trace API, with some major
> disadvantages. Profiler uses traces that produce rowsets so the UI can
> display those results to the user in a streaming fashion. It's a good
> option for seeing a short amount of activity on a server. Profiler is
> not intended for use, nor is it recommended for use, in an enterprise
> fashion because of the increased overhead and no guarantee of seeing all
> results.
> A server-side trace is a programmatic method to create, start, stop, and
> manage traces. The API (accessed through T-SQL) is somewhat verbose, but
> does provide a guarantee of seeing all traced information and causes
> much less overhead on the server.
> You use the sp_trace_* system procs and fn_trace_* system functions to
> do this.
> The easiest way to see the code for server-side trace is to set one up
> using Profiler. Select the options to save the results to a file and
> have the server manage the trace. Select the events you want and columns
> you need to see. Start the trace. Stop it. Select the File - Script
> Trace menu option and Profiler generates much of the code for you.
> You can then modify the code as you see fit. You'll also need to
> manually stop the trace, so getting the trace handle when the trace
> starts is important. You sp_trace_setstatus to start/stop traces.
> You always want to trace to a flat file on one of the server's local
> drives. Never to a network share or a NAS device. The tempdb/log device
> is usually a good place (RAID 5 arrays are not ideal for traces).
> When the trace is stopped, you can query the information from the file
> using fn_trace_gettable(), import the information into a table using
> that function, or open the trace file in Profiler.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||Just to comment on that:
Profiler limits amount of data displayed in TextData column in the grid,
however entire content of TextData is visible in the bottom pane once the
event is selected. It can be copied from there to clipboard using standard
Ctrl-C menu.
Excellent write-up on server side trace.
Regards,
Maciek Sarnowicz
SQL Server Tools SDE
This posting is provided "AS IS" with no warranties, and confers no rights
"JDArsenault" <JDArsenault@.discussions.microsoft.com> wrote in message
news:B5F43559-6464-4463-9A13-5BC1C2164E5F@.microsoft.com...[vbcol=seagreen]
> Thanks for the straight-forward help...
> "David Gugick" wrote: