INF: How to Create a SQL Server 7.0 Trace

Article translations Article translations
Article ID: 289742 - View products that this article applies to.
This article was previously published under Q289742
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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

REFERENCES

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

Properties

Article ID: 289742 - Last Review: November 2, 2013 - Revision: 2.0
Applies to
  • Microsoft SQL Server 7.0 Standard Edition
Keywords: 
kbnosurvey kbarchive kbDSupport kbinfo KB289742

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com