February 10, 2010 at 10:58 am
OK here's my prototype for scripting out the traces;
I created a procedure where you have to pass the trace id, and it seems to script everything out .
note this assumes that you are scripting something out to create it again as NEW; so for example, if you script out the default trace, and run the script, it creates a NEW trace (ie traceid 2,3, etc) that happens to have the same settings...but at least you could then tweak the code before executing it.
it worked just fine for my two traces i have, and the part i thougth was critical was to name what the commands are doing as a comment;
you might see stuff like this :
exec sp_trace_setevent @traceidout,55,6,@on --Hash Warning,NTUserName
exec sp_trace_setevent @traceidout,55,7,@on --Hash Warning,NTDomainName
and for a filter:
exec sp_trace_setfilter traceidout,10,0,6, N'SQLT%' -- AND ApplicationName LIKE SQLT%
exec sp_trace_setfilter traceidout,10,0,6, N'MS%' -- AND ApplicationName LIKE MS%
could you please play with it a bit and give me some feedback; it seems pretty close, i fixed a few things like default values if a trace haad some HULLS, but i only had three traces to test against.
--select * from sys.traces
--drop procedure sp_ScriptAnyTrace
--sp_ScriptAnyTrace 3
CREATE PROCEDURE [dbo].[sp_ScriptAnyTrace](@traceid int)
AS
BEGIN
SET NOCOUNT ON
declare @Results TABLE(ResultsId int identity(1,1),ResultsText varchar(max))
--i thought about using a results table, decided i wanted a single varchar max string instead,
--then had to revernt back to a results table because of concat truncation issues with implicit conversions to varchar(8000)
IF NOT EXISTS(SELECT 1 FROM sys.traces where id = @traceid)
BEGIN
insert into @Results(ResultsText)
SELECT 'No trace exists with ID = ' + convert(varchar,@traceid)
SELECT * FROM @Results order by ResultsID
RETURN
END
insert into @Results(ResultsText)
SELECT '--declare variables for parameterizing the command ' UNION ALL
SELECT 'declare @traceidout int ' UNION ALL
SELECT 'declare @options int ' UNION ALL
SELECT 'declare @path nvarchar(256) ' UNION ALL
SELECT 'declare @maxfilesize bigint ' UNION ALL
SELECT 'declare @maxRolloverFiles int ' UNION ALL
SELECT 'declare @stoptime datetime ' UNION ALL
SELECT 'declare @on bit ' UNION ALL
SELECT ' ' UNION ALL
SELECT 'set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.'
--script the settings from sys.traces
insert into @Results(ResultsText)
SELECT 'set @maxfilesize = ' + CASE WHEN max_size IS NULL THEN '20' ELSE CONVERT(varchar,max_size) END + ' --size in MB ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText)
SELECT 'set @maxRolloverFiles = ' + CASE WHEN max_files IS NULL THEN ' 5 ' ELSE CONVERT(varchar,max_files) END + ' --number of files; ie if 5 files, start rewriting on rollover ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText)
SELECT 'set @stoptime = ' + CASE WHEN stop_time IS NULL THEN 'NULL' ELSE '''' + CONVERT(varchar(40),stop_time,121)+ '''' END + ' -- null if never ends, else a specific date ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText)
SELECT 'set @options = 2' --rollover, ignore all other settings.
insert into @Results(ResultsText)
SELECT 'set @path = ''' + CASE WHEN path IS NULL THEN 'mytrace' ELSE LEFT(path,LEN(path) - 4) END + '''' + ' -- the trace adds ".trc" to the pathname, so avoiad "name.trc.trc" by removing it for scripting ' from sys.traces WHERE id =@traceid
insert into @Results(ResultsText) SELECT ''
--sp_trace_create [ @traceid = ] trace_id OUTPUT
--, [ @options = ] option_value
--, [ @tracefile = ] 'trace_file'
--[ , [ @maxfilesize = ] max_file_size ]
--[ , [ @stoptime = ] 'stop_time' ]
--[ , [ @filecount = ] 'max_rollover_files' ]
insert into @Results(ResultsText) SELECT ' --create the trace '
insert into @Results(ResultsText)
SELECT '--create the trace ' UNION ALL
SELECT 'exec sp_trace_create ' UNION ALL
SELECT ' @traceid = @traceidout output, ' UNION ALL
SELECT ' @options = @options, ' UNION ALL
SELECT ' @tracefile = @path, ' UNION ALL
SELECT ' @maxfilesize = @maxfilesize, ' UNION ALL
SELECT ' @stoptime = @stoptime, ' UNION ALL
SELECT ' @filecount = @maxRolloverFiles '
--details
insert into @Results(ResultsText) SELECT ''
insert into @Results(ResultsText) SELECT ' --for the Event Every SQL statement completed, capture columns of accessible data '
--exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName
insert into @Results(ResultsText)
SELECT ' exec sp_trace_setevent @traceidout,' + CONVERT(varchar(max),X.eventid) + ',' + CONVERT(varchar(max),X.columnid) + ',@on --'
+ E.Name + ',' + V.name
from ::fn_trace_geteventinfo(1) AS X
INNER JOIN sys.trace_events E ON X.eventid = E.trace_event_id
INNER JOIN sys.trace_columns V ON X.columnid = V.trace_column_id
insert into @Results(ResultsText) SELECT '--filters'
insert into @Results(ResultsText) SELECT ''
insert into @Results(ResultsText)
select ' exec sp_trace_setfilter traceidout' + ','
+ CONVERT(varchar,X.columnid) + ','
+ CONVERT(varchar,logical_operator) + ','
+ CONVERT(varchar,comparison_operator) + ','
+ ' N''' + CONVERT(varchar(8000),value) + ''' '
+ ' -- ' + CASE WHEN logical_operator = 0 THEN ' AND ' ELSE ' OR ' END + V.name
+ CASE
WHEN comparison_operator = 0 THEN ' = '
WHEN comparison_operator = 1 THEN ' <> '
WHEN comparison_operator = 2 THEN ' > '
WHEN comparison_operator = 3 THEN ' < '
WHEN comparison_operator = 4 THEN ' >= '
WHEN comparison_operator = 5 THEN ' <= '
WHEN comparison_operator = 6 THEN ' LIKE '
WHEN comparison_operator = 7 THEN ' NOT LIKE '
END
+ CONVERT(varchar(8000),value)
from
::fn_trace_getfilterinfo(@traceid) X
INNER JOIN sys.trace_columns V
ON X.columnid = V.trace_column_id
insert into @Results(ResultsText) SELECT '---final step'
insert into @Results(ResultsText) SELECT ''
insert into @Results(ResultsText) SELECT '--turn on the trace '
insert into @Results(ResultsText) SELECT ' exec sp_trace_setstatus @traceidout, 1 ---start trace '
insert into @Results(ResultsText) SELECT ' --exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it '
insert into @Results(ResultsText) SELECT ' --exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it '
SELECT * FROM @Results order by ResultsID
END
Lowell
February 10, 2010 at 11:13 am
Work of art.
it appears to work better than the profiler's script trace definition.
you're one smart dog, Lowell!
Craig Outcalt
February 10, 2010 at 12:04 pm
Lowell (2/10/2010)
OK here's my prototype for scripting out the traces;
Nice job!
November 30, 2010 at 11:57 am
Thanks, works great!
November 30, 2010 at 12:06 pm
Mike Good (11/30/2010)
Thanks, works great!
thank you for the feedback! glad it helped you out!
Lowell
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply