I tried to run
GO
SELECT * INTO temp_trc
FROM fn_trace_gettable('c:sqltrace20070402_4.trc' , default);
GO
I get an error how to correct this error
Msg 567, Level 16, State 1, Line 1
File 'c:sqltrace20070402_4.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.
Hi,Is there a way to read a trace file with fn_trace_gettable ifthat trace file resides in a path that contains a space? For example,test.trc is accessible with fn_trace_gettable if it resides in c: races, but throws an error if the trace file is in c:my traces.Thanks.-Nada
View 1 Replies View Related
I tried to trace all sql statements performed on a database. I don't use Profiler because this is a SQL Server version distributed with Studio 2008 Express. So I made a trace in TSQL, but most columns in the trace file are nulls...
Here is what I did to create this trace:
Create a new trace:
Code Block
DECLARE @TraceID INT
DECLARE @MaxSize bigint
SET @MaxSize = 15
EXECUTE sp_trace_create @TraceID output,
@options = 2,
@tracefile = N'C:Tracefile',
@maxfilesize = @MaxSize
(here @TraceID == 2)
Set up columns and events:
Code BlockDECLARE @TraceID int
DECLARE @On BIT
DECLARE @Event1 int
SET @On = 1
SET @TraceID = 2
SET @Event1 = 14
EXECUTE sp_trace_setevent @TraceID, @Event1, 1, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 2, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 4, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 6, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 8, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 9, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 11, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 14, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 15, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 18, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 22, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 27, @On
EXECUTE sp_trace_setevent @TraceID, @Event1, 28, @On
-- the same code for events 15, 40, 41, 42, 43, 50, 71, 72
END
Activate the trace:
Code BlockEXECUTE sp_trace_setstatus 2, 1
Then I entered some sql statements using both MS SQL Server Management Studio Express and my ADO.NET application written in Studio 2008 Express mainly by dragging and dropping (so it was autogenerated). I logged in and out.
Disactivated the trace:
Code BlockEXECUTE sp_trace_setstatus 2, 0
Finally the following statement:
Code BlockSELECT
TextData,BinaryData, TransactionID, NTUserName, HostName, ClientProcessID, LoginName, StartTime, EndTime, CPU, ObjectID, EventClass, ObjectType
FROM fn_trace_gettable('c:Tracefile.trc',default)
produced mostly null values, except StartTime and EventClass columns. The EventClass values were 65528, 65534, 65533.
So, the question is, how to get the sql statements that are performed on the server? What is wrong with this procedure? I think I picked up correct events (41 and 42) to show the statements. Besides.. I think all other columns should not be null as well. Why almost everything is null ?
Last question, what are these events 65528, 65534, 65533 ? There are no such numbers in tables sys.events and sys.trace_events and sys.trace_subclass_values. Also I couldn't find them in internet.