September 7, 2009 at 4:09 am
[font="Verdana"][/font]
This is my Stored Procedure
Create procedure LoginLogoutTrace_Sproc @Folder nvarchar(200)
as
set nocount on
-- To change the traces duration, modify the following statement
declare @StopTime datetime ; set @StopTime = dateadd(mi,540,getdate())
declare @StartDatetime varchar(13) ; set @StartDatetime =
convert(char(8),getdate(),112) + '_' +
cast(replace(convert(varchar(5),getdate(),108),':','') as char(4)) --['YYYYMMDD_HHMM']
declare @rc int
declare @TraceID int
declare @TraceFile nvarchar(100)
declare @MaxFileSize bigint ; set @MaxFileSize = 50 -- The maximum trace file in megabytes
declare @cmd nvarchar(2000)
declare @msg nvarchar(200)
If right(@Folder,1)<>'\' set @Folder = @Folder + '\'
-- Check if Folder exists
set @cmd = 'dir ' +@Folder
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'The specified folder ' + @Folder + '
does not exist, Please specify an existing drive:\folder '+ cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--Create new trace file folder
set @cmd = 'mkdir ' +@Folder+@StartDatetime
exec @rc = master..xp_cmdshell @cmd,no_output
if (@rc != 0) begin set @msg = 'Error creating trace folder : ' +
cast(@rc as varchar(10)) set @msg = @msg + 'SQL Server 2005 or later
instance require OLE Automation to been enabled' raiserror(@msg,10,1)
return(-1)
end
set @TraceFile = @Folder+@StartDatetime+'\LoginLogout_trace'
exec @rc = sp_trace_create @TraceID output, 2, @TraceFile,
@MaxFileSize, @StopTime
if (@rc != 0) begin set @msg = 'Error creating trace : ' + cast(@rc as
varchar(10)) raiserror(@msg,10,1) return(-1)
end
--> Using your saved trace file, add the '-- Set the events' section below <--
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 7, @on
exec sp_trace_setevent @TraceID, 14, 23, @on
exec sp_trace_setevent @TraceID, 14, 8, @on
exec sp_trace_setevent @TraceID, 14, 64, @on
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 25, @on
exec sp_trace_setevent @TraceID, 14, 41, @on
exec sp_trace_setevent @TraceID, 14, 49, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 26, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 35, @on
exec sp_trace_setevent @TraceID, 14, 51, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 60, @on
exec sp_trace_setevent @TraceID, 20, 7, @on
exec sp_trace_setevent @TraceID, 20, 23, @on
exec sp_trace_setevent @TraceID, 20, 31, @on
exec sp_trace_setevent @TraceID, 20, 8, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 60, @on
exec sp_trace_setevent @TraceID, 20, 64, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 9, @on
exec sp_trace_setevent @TraceID, 20, 49, @on
exec sp_trace_setevent @TraceID, 20, 6, @on
exec sp_trace_setevent @TraceID, 20, 10, @on
exec sp_trace_setevent @TraceID, 20, 14, @on
exec sp_trace_setevent @TraceID, 20, 26, @on
exec sp_trace_setevent @TraceID, 20, 3, @on
exec sp_trace_setevent @TraceID, 20, 11, @on
exec sp_trace_setevent @TraceID, 20, 35, @on
exec sp_trace_setevent @TraceID, 20, 51, @on
exec sp_trace_setevent @TraceID, 15, 7, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 23, @on
exec sp_trace_setevent @TraceID, 15, 8, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 64, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 41, @on
exec sp_trace_setevent @TraceID, 15, 49, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 26, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 35, @on
exec sp_trace_setevent @TraceID, 15, 51, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 60, @on
--> Using your saved trace file, add the '-- Set the Filters' section below <--
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 13
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 10, 1, 6, N'%SQL Server Management Studio%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%.Net SqlClient Data Provider%'
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'%MyworksDatabase%'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'%TempDB%'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'%Master%'
--> Customization is now completed <--
-----------------------------------------------------------------------------
-- This filter is added to exclude all profiler traces.
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler%'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1 -- start trace`
select 'Trace id = ', @TraceID, 'Path=', @Folder+@StartDatetime+'\'
return
go
Msg 19055, Level 16, State 1, Procedure sp_trace_setfilter, Line 1
Filters with the same event column ID must be grouped together.
September 7, 2009 at 4:11 am
I am getting this error, when i run my Sp.
I know which part is raising the error, but my question is that how to come out of this error?
Msg 19055, Level 16, State 1, Procedure sp_trace_setfilter, Line 1
Filters with the same event column ID must be grouped together.
The code part which is raising the error is given below
declare @intfilter int
declare @bigintfilter bigint
set @intfilter = 13
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 10, 1, 6, N'%SQL Server Management Studio%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'%.Net SqlClient Data Provider%'
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'%MyworksDatabase%'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'%TempDB%'
exec sp_trace_setfilter @TraceID, 35, 0, 7, N'%Master%'
Now how to i group the filters with the same event column ID ?
September 7, 2009 at 5:14 am
export the trace definition from profiler...i.e. select events and columns and apply filter in a profiler...and then File->export->script trace definition....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
September 7, 2009 at 6:01 am
Thanks
This Sp is created from trace script definition file itself. i was able able run the trace by using Profiler, when i try to run as a SP, the error is displayed
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply