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 ------

.

Relevant Pages

  • Profiler Trace
    ... I was able to script my profiler trace but when ... I ran it from Query Analyzer it quickly gave me a Trace Number. ... declare @TraceID int ... declare @bigintfilter bigint ...
    (microsoft.public.sqlserver.server)
  • Profiler Trace
    ... I was able to script my profiler trace but when ... I ran it from Query Analyzer it quickly gave me a Trace Number. ... declare @TraceID int ... declare @bigintfilter bigint ...
    (microsoft.public.sqlserver.server)
  • Profiler Trace issue
    ... I was able to script my profiler trace but when ... I ran it from Query Analyzer it quickly gave me a Trace Number. ... declare @TraceID int ... declare @bigintfilter bigint ...
    (microsoft.public.sqlserver.server)
  • Profiler Trace
    ... I was able to script my profiler trace but when ... I ran it from Query Analyzer it quickly gave me a Trace Number. ... declare @TraceID int ... declare @bigintfilter bigint ...
    (microsoft.public.sqlserver.server)
  • Profiler Trace issue
    ... I was able to script my profiler trace but when ... I ran it from Query Analyzer it quickly gave me a Trace Number. ... declare @TraceID int ... declare @bigintfilter bigint ...
    (microsoft.public.sqlserver.server)
Loading