-- Stored Procedure: dbo.trace2k
-- Bill Wunder

use admin
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[trace2k]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[trace2k]
GO

CREATE procedure dbo.trace2k
as
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @trace_name varchar(128)
declare @server_name varchar(128)
declare @object_name nvarchar(255)
declare @duration char(9) 

set @maxfilesize = 5 

set @duration = '000:59:00' -- 'hhh:mm:ss' 
set @trace_name = object_name(@@PROCID) + '_' + @@servername + '_'
  + cast(datepart(yyyy,getdate()) as varchar(4))
  + case when datalength(cast(datepart(mm,getdate()) as varchar(2))) = 1 then '0' else '' end
  + cast(datepart(mm,getdate()) as varchar(2))
  + case when datalength(cast(datepart(dd,getdate()) as varchar(2))) = 1 then '0' else '' end
  + cast(datepart(dd,getdate()) as varchar(2))
  + case when datalength(cast(datepart(hh,getdate()) as varchar(2))) = 1 then '0' else '' end
  + cast(datepart(hh,getdate()) as varchar(2))
  + case when datalength(cast(datepart(mi,getdate()) as varchar(2))) = 1 then '0' else '' end
  + cast(datepart(mi,getdate()) as varchar(2))
set @server_name = null
set @object_name = 'I:\' + @trace_name + '.TRC'

exec @rc = sp_trace_create @TraceID output, 2, @object_name, @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 33, 1, @on
exec sp_trace_setevent @TraceID, 33, 12, @on
exec sp_trace_setevent @TraceID, 33, 14, @on
exec sp_trace_setevent @TraceID, 33, 21, @on
exec sp_trace_setevent @TraceID, 33, 25, @on
exec sp_trace_setevent @TraceID, 33, 31, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 21, @on
exec sp_trace_setevent @TraceID, 40, 25, @on
exec sp_trace_setevent @TraceID, 40, 31, @on
exec sp_trace_setevent @TraceID, 44, 1, @on
exec sp_trace_setevent @TraceID, 44, 12, @on
exec sp_trace_setevent @TraceID, 44, 14, @on
exec sp_trace_setevent @TraceID, 44, 21, @on
exec sp_trace_setevent @TraceID, 44, 25, @on
exec sp_trace_setevent @TraceID, 44, 31, @on
exec sp_trace_setevent @TraceID, 61, 1, @on
exec sp_trace_setevent @TraceID, 61, 12, @on
exec sp_trace_setevent @TraceID, 61, 14, @on
exec sp_trace_setevent @TraceID, 61, 21, @on
exec sp_trace_setevent @TraceID, 61, 25, @on
exec sp_trace_setevent @TraceID, 61, 31, @on
exec sp_trace_setevent @TraceID, 67, 1, @on
exec sp_trace_setevent @TraceID, 67, 12, @on
exec sp_trace_setevent @TraceID, 67, 14, @on
exec sp_trace_setevent @TraceID, 67, 21, @on
exec sp_trace_setevent @TraceID, 67, 25, @on
exec sp_trace_setevent @TraceID, 67, 31, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- get_%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- gs_silo%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- web%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- wh_%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- goldman%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'exec%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'set textsize 100000'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'SET ROWCOUNT 0'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'select "Success" = 1'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- f%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- noncusip%'

exec sp_trace_setfilter @TraceID, 10, 1, 6, N'Tunnels'
set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter

-- Set the trace status to start
exec @rc = sp_trace_setstatus @TraceID, 1
if (@rc != 0) goto error

-- display trace id for future references
raiserror ('Trace ''%s'' is started. TraceId = %d',1,1,@trace_name, @TraceId) WITH NOWAIT, LOG
--select TraceID=@TraceID

WAITFOR DELAY @duration

exec @rc = sp_trace_setstatus @TraceID, 0
exec @rc = sp_trace_setstatus @TraceID, 2


goto finish

error: 
select ErrorCode=@rc

finish:
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON 
GO