--Listing 3. spTraceBuild
USE msdb
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'spTraceBuild' and type = 'P')
DROP PROC spTraceBuild
GO
-------------------------------------------------------------------------------------------------------
-- Name spTraceBuild
-- Description Starts a SQL Server trace. The code based on sp build_trace from the Microsoft
-- Knowledge Base Article Q283790.
-- Input From 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
-- Modified By Viktor Gorodnichenko
-------------------------------------------------------------------------------------------------------
CREATE PROC spTraceBuild
@traceini nvarchar (245) = N'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\ActivityTrace.ini'
as
SET NOCOUNT ON
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'
SET @stoptime = CONVERT(datetime, CONVERT(varchar(20),getdate(),107) + ' ' + @stoptime)
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 = @tracefile + '_' + @@SERVERNAME + REPLACE(LEFT(CONVERT(varchar(20),getdate(),107),6),' ','')
--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
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_create failed. Unknown error.'
WHEN @rc = 10 THEN 'sp_trace_create failed. Invalid options. Options specified are incompatible.'
WHEN @rc = 12 THEN 'sp_trace_create failed. File not created.'
WHEN @rc = 13 THEN 'sp_trace_create failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_create failed. Invalid stop time. The stop time specified has already happened.'
WHEN @rc = 15 THEN 'sp_trace_create failed. Invalid parameters. Supplied incompatible parameters.'
END
RETURN
END
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 @rc = sp_trace_setevent @traceid, @event, @column, @on
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setevent failed. Unknown error.'
WHEN @rc = 2 THEN 'sp_trace_setevent failed. The trace is currently running.'
WHEN @rc = 3 THEN 'sp_trace_setevent failed. The specified Event is not valid. The Event may not exist or it
is not an appropriate one for the store procedure.'
WHEN @rc = 4 THEN 'sp_trace_setevent failed. The specified Column is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setevent failed. The specified Trace Handle is not valid.'
WHEN @rc = 11 THEN 'sp_trace_setevent failed. The specified Column is used internally and cannot be removed. '
WHEN @rc = 13 THEN 'sp_trace_setevent failed. Out of memory. There is not enough memory to perform the specified action.'
WHEN @rc = 14 THEN 'sp_trace_setevent failed. The function is not valid for this trace.'
END
RETURN
END
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 @rc = sp_trace_setstatus @traceid, 1
IF @rc <> 0
BEGIN
PRINT
CASE
WHEN @rc = 1 THEN 'sp_trace_setstatus. Unknown error.'
WHEN @rc = 8 THEN 'sp_trace_setstatus. The specified Status is not valid.'
WHEN @rc = 9 THEN 'sp_trace_setstatus. The specified Trace Handle is not valid.'
WHEN @rc = 13 THEN 'sp_trace_setstatus. Out of memory. Returned when there is not enough memory to perform the specified action.'
END
RETURN
END
SELECT 'Trace '+LTRIM(STR(@traceid)) + ' has been lanched. The trace details:'
SELECT * FROM ::fn_trace_getinfo (@traceid)
GO
exec spTraceBuild
donderdag 20 november 2008
Abonneren op:
Reacties posten (Atom)
Geen opmerkingen:
Een reactie posten