ITPub博客

首页 > Linux操作系统 > Linux操作系统 > trace build

trace build

原创 Linux操作系统 作者:roachwong 时间:2011-08-29 10:08:20 0 删除 编辑

CREATE   proc trace_build  @traceini nvarchar (245) = N'C:\ActivityTrace.ini' as
declare @traceid int, @options int, @tracefile nvarchar (245), @maxfilesize bigint
      , @stoptime datetime, @minMBfree bigint, @rc int, @on bit, @cmd1 nvarchar(512)
      , @events varchar(512), @columns varchar(512), @event int, @column int, @estart int, @enext int
      , @cstart int, @cnext int, @le int, @lc int, @filter nvarchar(245), @filter_num int
create table #t1 ([c1] nvarchar(512))
set @cmd1 = 'bulk insert #t1 FROM '''
select @cmd1 + @traceini
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 @maxfilesize = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as bigint) from #t1 where left(c1,3) = '@ma'
select @stoptime = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as datetime) from #t1 where left(c1,3) = '@st'
select @options = cast(rtrim(ltrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as int) from #t1 where left(c1,3) = '@op'
select @events=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512)) from #t1 where left(c1,3) = N'@ev'
select @columns=cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (512)) from #t1 where left(c1,3) = N'@co'
set @on = 1
set @traceid = 0
select @tracefile
SELECT @traceid = traceid FROM :: fn_trace_getinfo(0) where property = 2 and value = @tracefile
if @traceid != 0 goto finish
set @cmd1 = 'if exist ' + @tracefile + '.trc ' + 'del ' + @tracefile + '*.trc'
exec @rc = master.dbo.xp_cmdshell @cmd1, no_output
exec @rc = sp_trace_create @traceid output, @options, @tracefile, @maxfilesize, @stoptime

select @estart = 1
select @enext = charindex(',',@events,@estart)
select @cstart = 1
select @cnext = charindex(',',@columns,@cstart)
set @le = len(@events)
set @lc = len(@columns)
while @enext > 0
 begin
 select @event = cast(substring(@events,@estart,@enext-@estart) as int)
 while @cnext > 0
  begin
  select @column = cast(substring(@columns,@cstart,@cnext-@cstart) as int)
  exec sp_trace_setevent @traceid, @event, @column, @on
  select @cstart = @cnext + 1
  select @cnext = charindex(',',@columns,@cstart)
  if @cnext = 0 set @cnext = @lc + 1
  if @cstart >@lc set @cnext = 0
  end
 select @cstart = 1
 select @cnext = charindex(',',@columns,@cstart)
 select @estart = @enext + 1
 select @enext = charindex(',',@events,@estart)
 if @enext = 0 set @enext = @le + 1
 if @estart > @le set @enext = 0
 end
set @cmd1 = 'exec sp_trace_setfilter '
set @filter = N'none'
select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245))
 from #t1
 where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245)) = N'@filter1'
set @filter_num = 1
while @filter != N'none'
 begin
 exec (@cmd1 + @traceid + ','+@filter)
 set @filter_num = @filter_num + 1
 set @filter = N'none'
 select @filter = cast(ltrim(rtrim(substring(c1,charindex('=',c1,1)+1,len(c1)))) as nvarchar (245)) from #t1
 where cast(ltrim(rtrim(substring(c1,1,charindex('=',c1,1)-1))) as nvarchar (245)) = N'@filter' + cast(@filter_num as nvarchar(3))
 select @filter
 end
finish:
drop table #t1
--exec sp_trace_setstatus @traceid, 1
    

--------------------------------------------------------------------------------


GO

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/90986/viewspace-706107/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Trace sp
下一篇: trace configuration
请登录后发表评论 登录
全部评论

注册时间:2011-04-20

  • 博文量
    46
  • 访问量
    51717