July 9, 2004 at 7:44 am
OK, the Subject should be "Why can't I run a trace at startup?", but the site won't let me edit it.
I can't get my trace (in a stored procedure) to run at startup. Well, it starts, but it has a status of 0.
I used:
exec sp_procoption N'sp_SarBoxAudit', N'startup', N'on'
AFter I start the server and run:
SELECT * FROM :: fn_trace_getinfo(1)
The output is:
1 1 6
1 2 c:\SQLreports\SecurityAudit
1 3 5
1 4 NULL
1 5 0
The last 0 indicates a status of off.
Does anyone know how to make the status = 1 on startup?
July 9, 2004 at 9:48 am
Can you post stored procedure "sp_SarBoxAudit"?
July 9, 2004 at 9:58 am
CREATE PROCEDURE sp_SarBoxAudit AS
/****************************************************/
/* Created by: SQL Profiler */
/* Date: 07/08/2004 01:37:26 PM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 2, N'c:\SQLreports\SARBOXaudit', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
-- lots of other events
exec sp_trace_setevent @TraceID, 118, 40, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
--exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
--exec sp_trace_setfilter @TraceID, 11, 1, 6, N'Domain\Test'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
GO
July 9, 2004 at 11:11 am
create procedure sp2000_sarboxaudit as
/****************************************************/
/* created by: sql profiler */
/* date: 07/08/2004 01:37:26 pm */
/****************************************************/
-- create a queue
declare @rc int
declare @traceident int
declare @traceid int
declare @maxfilesize bigint
set @maxfilesize = 1
declare @path nvarchar(245)
select @path = 'c:\temp\test_20040709'
-- please replace the text insertfilenamehere, with an appropriate
-- filename prefixed by a path, e.g., c:\myfolder\mytrace. the .trc extension
-- will be appended to the filename automatically. if you are writing from
-- remote server to local drive, please use unc path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @traceid = @traceident output, @options = 2,
@tracefile = @path,
@maxfilesize = @maxfilesize,
@stoptime = null
if (@rc != 0) goto error
-- client side file and table cannot be scripted
-- set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @traceident, 10, 1, @on
-- lots of other events
exec sp_trace_setevent @traceident, 118, 40, @on
-- set the filters
declare @intfilter int
declare @bigintfilter bigint
--exec sp_trace_setfilter @traceid, 10, 0, 7, n'sql profiler'
--exec sp_trace_setfilter @traceid, 11, 1, 6, n'domain\test'
-- set the trace status to start
exec sp_trace_setstatus @traceid = @traceident, @status = 1
-- display trace id for future references
goto finish
error:
print "aaa"
print cast(@traceid as varchar(2))
select errorcode=@rc
finish:
go
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply