SQL Server 2005 - Server side trace script

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

------ BEGIN SCRIPT ------
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server
has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',
@maxfilesize, NULL
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler -
5dba9f9a-7e18-4f26-8fb2-c73b7ddc6687'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

------ END SCRIPT ------

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" wrote in message

> 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
>
> ------ BEGIN SCRIPT ------
> -- Create a Queue
> declare @rc int
> declare @TraceID int
> declare @maxfilesize bigint
> set @maxfilesize = 5
>
> -- Please replace the text InsertFileNameHere, with an appropriate
> -- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc
> extension
> -- will be appended to the filename automatically. If you are writing from
> -- remote server to local drive, please use UNC path and make sure server
> has
> -- write access to your network share
>
> exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere',
> @maxfilesize, NULL
> if (@rc != 0) goto error
>
> -- Client side File and Table cannot be scripted
>
> -- Set the events
> declare @on bit
> set @on = 1
> exec sp_trace_setevent @TraceID, 10, 15, @on
> exec sp_trace_setevent @TraceID, 10, 16, @on
> exec sp_trace_setevent @TraceID, 10, 1, @on
> exec sp_trace_setevent @TraceID, 10, 9, @on
> exec sp_trace_setevent @TraceID, 10, 17, @on
> exec sp_trace_setevent @TraceID, 10, 10, @on
> exec sp_trace_setevent @TraceID, 10, 18, @on
> exec sp_trace_setevent @TraceID, 10, 11, @on
> exec sp_trace_setevent @TraceID, 10, 12, @on
> exec sp_trace_setevent @TraceID, 10, 13, @on
> exec sp_trace_setevent @TraceID, 10, 6, @on
> exec sp_trace_setevent @TraceID, 10, 14, @on
>
>
> -- Set the Filters
> declare @intfilter int
> declare @bigintfilter bigint
>
> exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler -
> 5dba9f9a-7e18-4f26-8fb2-c73b7ddc6687'
> -- Set the trace status to start
> exec sp_trace_setstatus @TraceID, 1
>
> -- display trace id for future references
> select TraceID=@TraceID
> goto finish
>
> error:
> select ErrorCode=@rc
>
> finish:
> go
>
> ------ END SCRIPT ------