Nice Thing

This is similar procedure for starting trace

that I'm using in daily processing.

CREATE PROCEDURE sp_startTrace

AS

DECLARE @TraceIdOut INT

DECLARE @FolderTracenvarchar(128)

DECLARE @FileTraceLikenvarchar(128)

DECLARE @FileTracenvarchar(128)

SELECT @FolderTrace = 'C:\FolderTrace\'

SET @FileTraceLike = @FolderTrace + 'Trace%'

IF

(select count(*) from ::fn_trace_getinfo(default)

where CAST(value AS VARCHAR) like cast(@FileTraceLike AS VARCHAR))

> 0

BEGIN

PRINT 'Already started'

RETURN

END

--------------trace file name TraceYYMMDDHHMMSS

SET @FileTrace = @FolderTrace + 'Trace' +

(select convert(CHAR(6),Getdate(), 12)) +

SUBSTRING((select convert(CHAR(8),Getdate(), 108)),1,2)+

SUBSTRING((select convert(CHAR(8),Getdate(), 108)),4,2)+

SUBSTRING((select convert(CHAR(8),Getdate(), 108)),7,2)

DECLARE @onValue as bit

SET @onValue = 1

DECLARE @maxfilesizeValue bigint

SET @maxfilesizeValue = 1

exec Master..sp_trace_create @TraceIdOut OUT,

@Options =2,

@TraceFile = @FileTrace,

@maxfilesize = @maxfilesizeValue

DECLARE Events_Cursor CURSOR FOR

SELECT 10 As EventID

UNION SELECT 11

UNION SELECT 12

UNION SELECT 13

UNION SELECT 16

UNION SELECT 17

UNION SELECT 21

UNION SELECT 22

UNION SELECT 25

UNION SELECT 33

UNION SELECT 37

UNION SELECT 40

UNION SELECT 41

UNION SELECT 42

UNION SELECT 43

UNION SELECT 53

UNION SELECT 55

UNION SELECT 59

UNION SELECT 61

UNION SELECT 67

UNION SELECT 69

UNION SELECT 74

UNION SELECT 78

UNION SELECT 79

UNION SELECT 80

UNION SELECT 92

UNION SELECT 93

UNION SELECT 100

OPEN Events_Cursor

DECLARE @EventID INT

FETCH NEXT FROM Events_Cursor INTO @EventID

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE Columns_Cursor CURSOR FOR

SELECT 1 AS ColID

UNION SELECT 3

UNION SELECT 6

UNION SELECT 8

UNION SELECT 10

UNION SELECT 11

UNION SELECT 12

UNION SELECT 13

UNION SELECT 14

UNION SELECT 15

UNION SELECT 18

UNION SELECT 27

UNION SELECT 40

OPEN Columns_Cursor

DECLARE @ColumnID INT

FETCH NEXT FROM Columns_Cursor INTO @ColumnID

WHILE @@FETCH_STATUS = 0

BEGIN

exec Master..sp_trace_setevent @TraceId = @TraceIdOut,

@EventID = @EventID ,

@columnid = @ColumnID ,

@on =@onValue

FETCH NEXT FROM Columns_Cursor INTO @ColumnID

END

CLOSE Columns_Cursor

DEALLOCATE Columns_Cursor

FETCH NEXT FROM Events_Cursor INTO @EventID

END

CLOSE Events_Cursor

DEALLOCATE Events_Cursor

----------- Filter

exec Master..sp_trace_setfilter @traceid = @TraceIdOut,

@columnid = 3,

@logical_operator = 0,

@comparison_operator = 0,

@value = 15

exec Master..sp_trace_setfilter @traceid = @TraceIdOut,

@columnid = 10,

@logical_operator = 0,

@comparison_operator = 7,

@value = N'SQL Profiler'

exec Master..sp_trace_setstatus@traceid = @TraceIdOut ,

@status = 1

print ' Started Trace ' + @FileTrace

GO