You are currently offline, waiting for your internet to reconnect

INF: How to Create a SQL Server 7.0 Trace

This article was previously published under Q289742
This article has been archived. It is offered "as is" and will no longer be updated.
The stored procedure that follows, trace_build7, creates a trace from arguments in a text file. The trace_build7 stored procedure is an alternative for using SQL Profiler to create a script to create a trace.

The syntax for trace_build7 is:
trace_build7  @traceini				
@traceini is the name of a text file that contains information for creating a trace. The file name is defined with a data type of nvarchar(245) and has a default value of "C:\ActivityTrace7.ini".
More information
The following example creates a trace from information contained in the Trace1.ini file:
trace_build 'c:\Trace1.ini'				
The @traceini file must contain the following information:
  • @tracefile

    Data is written to the trace file specified in this @tracefile variable. Change @tracefile to save the trace to a different drive and file name. Note that a .trc extension is automatically added by the trace.
  • @events

    Change @events to control the events that are traced.
Run the following statement in a query window to get a list of event names and event class numbers:
For example, consider the following contents of a @traceini file that is named TraceArgs.ini:
@tracefile         = C:\ActivityTrace7 @events            = 10,11,12,13,16,17,19,33,42,43,55 				
When the following statement is executed:
trace_build7 'C:\TraceArgs.ini'				
The script creates a tracefile, C:\ActivityTrace7.trc (@tracefile), which contains the trace. The following events are traced (@events):
  • RPC:Completed
  • RPC:Starting
  • SQL:BatchCompleted
  • SQL:BatchStarting
  • Attention
  • ExistingConnection
  • DTCTransaction
  • Exception
  • SP:Starting
  • SP:Completed
  • Hash Warning
All the columns for the preceding events are traced.

For more information about stopping or checking the status of the trace, see the following article in the Microsoft Knowledge Base:
289279 INF: How to Monitor SQL Server 7.0 Traces
Trace_build7 Stored Procedure
CREATE proc trace_build7  @traceini nvarchar (245) = N'C:\ActivityTrace7.ini' as set nocount ondeclare @traceid int, @tracefile nvarchar (245), @rc int, @cmd1 nvarchar(512)      , @events varchar(512), @event int, @estart int, @enext int, @le intcreate table #t1 ([c1] nvarchar(512))set @cmd1 = 'bulk insert #t1 FROM '''exec (@cmd1 + @traceini + '''')select @tracefile = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1 where left(c1,3) = '@tr'select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512)) from #t1 where left(c1,3) = N'@ev'set @traceid = 0create table #traces (traceid int)insert #traces EXEC master.dbo.xp_trace_enumqueuehandlescreate table #_dest (Destination varchar(256)                  , [On] int, Server sysname NULL, Object sysname NULL)while (select top 1 count(*) from #traces) > 0 begin  select top 1 @traceid = traceid from #traces  insert #_dest exec master.dbo.xp_trace_getqueuedestination @traceid  if (select top 1 count(*) from #_dest where object = @tracefile+'.trc') > 0   begin   select 'Cannot create new trace for same destination,' + object          + ', as trace number ' + cast(@traceid as varchar(3)) as [Destination]     from #_dest where object is not null   goto finish   end  truncate table #_dest  delete #traces where traceid = @traceid	enddrop table #_destdrop table #tracesset @cmd1 = 'if exist ' + @tracefile + '.trc ' + 'del ' + @tracefile + '.trc'exec @rc = master.dbo.xp_cmdshell @cmd1, no_outputEXEC master.dbo.xp_trace_addnewqueue 3000, 6000, 95, 90, 129761279, @traceid OUTPUTselect @estart = 1select @enext = charindex(',',@events,@estart)set @le = len(@events)while @enext > 0 begin select @event = cast(substring(@events,@estart,@enext-@estart) as int) EXEC master.dbo.xp_trace_seteventclassrequired @traceid, @event, 1 select @estart = @enext + 1 select @enext = charindex(',',@events,@estart) if @enext = 0 set @enext = @le + 1 if @estart > @le set @enext = 0 endset @tracefile = @tracefile + '.trc'EXEC master.dbo.xp_trace_setqueuedestination @traceid, 2, 1, NULL,@tracefileEXEC master.dbo.xp_trace_startconsumer @traceidfinish:drop table #t1set nocount off				


SQL Server 7.0 Books Online; topics: "xp_trace_seteventclassrequired (T-SQL)"; "xp_trace_eventclassrequired (T-SQL)"; "xp_trace_geteventnames (T-SQL)"

Article ID: 289742 - Last Review: 11/02/2013 00:44:00 - Revision: 2.0

Microsoft SQL Server 7.0 Standard Edition

  • kbnosurvey kbarchive kbDSupport kbinfo KB289742