Hi,

I have been beating my head against the wall on this one, and am hoping
someone can point out what I am doing wrong here.

I am trying to set up a trace (using script) that will tracks all the
"RPC:Completed" events and give me some of the vital statistics like
TextData, StartTime, EndTime, Duration, Reads, Writes, CPU, etc. To make
sure I didn't screw this up, I first set the trace up in Profiler and
exported the trace definition (File->Export->Script Trace Definition->SQL
Server 2005) to a file. I have cut and paste the script I get at the end.

The problem I have is that when I open the trace output in Profiler, I don't
see the TextData column - instead, I see the BinaryData column. This appears
to be happening only when tracing the "RPC:Completed" event. In other words,
if I use the same script and replace EventID "10" with a "12" in the
sp_trace_setevent calls, the TextData column is included in the output file.

Has anyone seen the same behavior or is this just me? My environment is
SQL2005 64-bit, SP1 on Windows 2003 R2 (64-bit). Thanks in advance for your
help.

Anand

Anand,

By default Profiler will not have the TextData column checked for the RPC
events since it can get that information from the binary data column which
is usually more efficient. To make things consistent I usually just include
the textdata column and exclude the binary (unless I need it for something
else) when I create the trace. So just add the following line to your trace
script and you should be fine:

exec sp_trace_setevent @TraceID, 10, 1, @on

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Anand" hotmail.com> wrote in message
news:ei0Z%%23Jl8IHA.1080@TK2MSFTNGP06.phx.gbl...

Thanks for you reply Andrew. My problem is that I do have "exec
sp_trace_setevent @TraceID, 10, 1, @on" in my script. I do not have "exec
sp_trace_setevent @TraceID, 10, 2, @on". But I still don't get the TextData
column in the output, but instead see the BinaryData column. In other words,
no matter what I do, I simply cannot seem to get the TextData column in the
output when tracing EventID 10!

Anand

"Andrew J. Kelly" shadhawk.com> wrote in message
news:OrLPFfl8IHA.4108@TK2MSFTNGP04.phx.gbl...

...

I am a little confused by what you mean by "output". Do you mean the
textdata column does not show up in Profiler? If so did you ensure the
column is selected in the list after you load the trace file? What happens
when you select directly from the file?

SELECT * FROM fn_trace_gettable('c:\MyFolder\MyTrace.trc', DEFAULT)

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Anand" hotmail.com> wrote in message
news:e6NTg5l8IHA.2416@TK2MSFTNGP02.phx.gbl...

I meant the output generated by the trace (the .trc file). I did not open it
using the function - I was opening the .trc file using Profiler to see what
was logged.

Will let you know what I get if I read the "output" using the function :-)

Anand

"Andrew J. Kelly" shadhawk.com> wrote in message
news:#7PB$jm8IHA.1568@TK2MSFTNGP04.phx.gbl...

Hi Andrew,

When I read the .trc file using the fn_trace_gettable function, I DO see the
TextData column!! If I open the same file using Profiler, I don't see that
column. I guess this is some kind of feature in Profiler that I was not
aware of :-)

Thanks a lot for your help. I have what I need now, although I am still
confused as to why Profiler does not show that column.

Anand

"Anand" hotmail.com> wrote in message
news:uj8qh8p8IHA.4988@TK2MSFTNGP04.phx.gbl...

I will ask again. Did you ensure that the TextData column is checked off as
one of the columns in Profiler in the first place? You can add or remove
any columns or events that you want and TextData is not one of the selected
columns by default for an RPC event.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Anand" hotmail.com> wrote in message
news:%%239bBN9x8IHA.4884@TK2MSFTNGP05.phx.gbl...

Andrew J. Kelly (sqlmvpnooospam@shadhawk.com) writes:

Actually, I seem to recall having made the same observation as Anand.
If I have a server-side trace, and open it in Profiler, TextData is
missing for some RPC events, although it's returned by fn_trace_gettable.
It was a while since I ran into this, but it may be that if I include
BinaryData in the trace, this does not happen.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx