Re: Profiler Bug viewing trace flat file?

BTW, you never said what version you are using. You should always do this
when reported possible bugs.
I tested this on SQL 2005. If it is a problem with SQL 2000, I can't test
that.

If you are using SQL 2005, you should use sys.traces instead of
fn_trace_getinfo. It's a lot easier to work with and has a lot more
information.

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com


"Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message
news:630D3BC9-F968-402C-AEF8-3C48A5BE1E03@xxxxxxxxxxxxxxxx
I'm collecting from 30 servers and they're all the same. Its not a major
problem as all the trace files get loaded into SQL through
fn_trace_gettable
and there we get the TextData. I do open the odd one up through profiler
though and this is where I notice the problem.

I guess you are correct in that fn_trace_gettable may derive the Textdata
from the binary data.

Here is my code. Try and you will see:

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

exec @rc = sp_trace_create @TraceID output, 0, N'c:\mytrace',
@maxfilesize,
NULL
if (@rc != 0) goto error

-- 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, 1, 6, N'myfilter'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler -
7412f6af-1549-44c6-bc15-4d0e9dbc9d61'
-- 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


-- stop the trace
declare @trace_id INT
select @trace_id = 0

select @trace_id = traceid
from fn_trace_getinfo (NULL)
where value = 'c:\mytrace.trc'

IF @trace_id <> 0
BEGIN
EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 0
EXEC master..sp_trace_setstatus @traceid = @trace_id ,@status = 2
END



--
-- cranfield, DBA


"Andrew J. Kelly" wrote:

That function will return all the columns regardless of if they were in
the
original trace or not. Most will simply be null though. It may be that
the
function is getting the data from the Binary column to display in the
textdata but this sounds really weird since you say that Textdata is in
the
trace definition. Are you on the latest service pack? Have you tried
creating a new trace definition and file from scratch to see if it is
just
this one file?

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message
news:D9A75649-5AD5-4A55-9B1A-10A668B7A3B1@xxxxxxxxxxxxxxxx
Hi Kalen

No, the TextData column is not in the properties in Profiler.

When I select from the file using fn_trace_gettable, TextData is the
first
column returned and has the data I require.

--
-- cranfield, DBA


"Kalen Delaney" wrote:

Can you look in the trace properties once you open it in Profiler to
see
if
text data is there?

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com


"Cranfield" <alan_cranfield@xxxxxxxxx> wrote in message
news:4FC6DE47-D82F-4933-A9FE-EF34F01A3ACD@xxxxxxxxxxxxxxxx
Hello

I have recorded a Profiler trace to a flat file using the sp_trace
procs.

When I query the flat file using the fn_trace_gettable i can see the
TextData field but when I open the flat file through the Profiler
GUI I
dont
see the TextData field.

These are the events I'm logging:

-- 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, 2, @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

is this a BUG or is there a way to see the Textdata field through
Profiler?

tks
--
-- cranfield, DBA







.

Relevant Pages

  • Re: Profiler Bug viewing trace flat file?
    ... Kalen Delaney, SQL Server MVP ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata ...
    (microsoft.public.sqlserver.tools)
  • Re: Profiler Bug viewing trace flat file?
    ... The TextData column is definitely populated when I query resulting trace ... selected in Profiler and run from Profiler. ... declare @TraceID int ...
    (microsoft.public.sqlserver.tools)
  • Re: Profiler Bug viewing trace flat file?
    ... sure you are actually seeing the TextData column populated? ... problem as all the trace files get loaded into SQL through ... I do open the odd one up through profiler ... declare @TraceID int ...
    (microsoft.public.sqlserver.tools)
  • Re: Profiler Bug viewing trace flat file?
    ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata field ...
    (microsoft.public.sqlserver.tools)
  • Re: Profiler Bug viewing trace flat file?
    ... problem as all the trace files get loaded into SQL through fn_trace_gettable ... I guess you are correct in that fn_trace_gettable may derive the Textdata ... declare @TraceID int ... the TextData column is not in the properties in Profiler. ...
    (microsoft.public.sqlserver.tools)