Microsoft KB Archive/289742

= INF: How to Create a SQL Server 7.0 Trace =

Article ID: 289742

Article Last Modified on 7/16/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q289742



SUMMARY
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 &quot;C:\ActivityTrace7.ini&quot;.



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: master..xp_trace_geteventnames 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 on declare @traceid int, @tracefile nvarchar (245), @rc int, @cmd1 nvarchar(512) , @events varchar(512), @event int, @estart int, @enext int, @le int create 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 = 0 create table #traces (traceid int) insert #traces EXEC master.dbo.xp_trace_enumqueuehandles

create 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 end drop table #_dest drop table #traces set @cmd1 = 'if exist ' + @tracefile + '.trc ' + 'del ' + @tracefile + '.trc' exec @rc = master.dbo.xp_cmdshell @cmd1, no_output EXEC master.dbo.xp_trace_addnewqueue 3000, 6000, 95, 90, 129761279, @traceid OUTPUT select @estart = 1 select @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 end set @tracefile = @tracefile + '.trc' EXEC master.dbo.xp_trace_setqueuedestination @traceid, 2, 1, NULL,@tracefile EXEC master.dbo.xp_trace_startconsumer @traceid finish: drop table #t1 set nocount off