Sorry for late
Copy it and paste it in query analyzer
Query Analyzer Script:
====================================
You Should create this table :
USE [master]
GO
/****** Object: Table [dbo].[USP_trace_Queue] Script Date: 12/04/2008 13:41:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[USP_trace_Queue](
[TraceId] [int] NULL,
[TraceName] [varchar](40) COLLATE Arabic_CI_AS NULL,
[TraceFile] [sysname] COLLATE Arabic_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
insert into dbo.USP_trace_Queue
(TraceId,TraceName,TraceFile)
values (4,'Login Creation on SQL-Server1','F:\Microsoft SQL Server\MSSQL\Audit\Login Creation on SQL-Server1 20081203233001410')
====================================================
Procedure Code:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_trace_login_creation] Script Date: 12/04/2008 13:38:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROC [dbo].[usp_trace_login_creation]
@OnOff varchar(4)='/?',
@file_name sysname=NULL,
@TraceName sysname=NULL,
@Options int=2,
@MaxFileSize bigint=4000,
@StopTime datetime=NULL,
@TraceType int=0,
@Events varchar(300)=
-- Default values
'11,13,14,15,16,17,33,42,43,45,55,67,69,79,80',
@Cols varchar(300)=
-- All columns
'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,',
@IncludeTextFilter sysname=NULL,
@ExcludeTextFilter sysname=NULL,
@IncludeObjIdFilter int=NULL,
@ExcludeObjIdFilter int=NULL,
@IncludeObjNameFilter sysname=NULL,
@ExcludeObjNameFilter sysname=NULL,
@IncludeHostFilter sysname=NULL,
@ExcludeHostFilter sysname='%Query%',
@TraceId int = NULL
AS
BEGIN
SET NOCOUNT ON
IF @OnOff='/?' GOTO Help
SET @OnOff=UPPER(@OnOff)
IF (@OnOff='LIST') BEGIN
IF (OBJECT_ID('master..USP_trace_Queue') IS NOT NULL) BEGIN
IF (@TraceId IS NULL) BEGIN
DECLARE tc CURSOR FOR SELECT * FROM master..USP_trace_Queue FOR READ ONLY
DECLARE @tid int, @tname varchar(30), @tfile sysname
OPEN tc
FETCH tc INTO @tid, @tname, @tfile
IF @@ROWCOUNT<>0 BEGIN
WHILE @@FETCH_STATUS=0 BEGIN
SELECT TraceId, TraceName, TraceFile FROM master..USP_trace_Queue WHERE
TraceId=@tid
SELECT * FROM ::fn_trace_getinfo(@tid)
FETCH tc INTO @tid, @tname, @tfile
END
END ELSE PRINT 'No traces in the trace queue.'
CLOSE tc
DEALLOCATE tc
END ELSE BEGIN
SELECT TraceId, TraceName, TraceFile FROM master..USP_trace_Queue WHERE TraceId=@TraceId
SELECT * FROM ::fn_trace_getinfo(@TraceId)
END
END ELSE PRINT 'No traces to list.'
RETURN 0
END
-- Declare variables
DECLARE @OldQueueHandle int -- Queue handle of currently running trace queue
DECLARE @QueueHandle int -- Queue handle for new running trace queue
DECLARE @On bit
DECLARE @OurObjId int -- Used to keep us out of the trace log
DECLARE @OldTraceFile sysname -- File name of running trace
DECLARE @res int -- Result var for sp calls
SET @On=1
-- Stop the trace if running
IF OBJECT_ID('master..USP_trace_Queue') IS NOT NULL BEGIN
IF EXISTS(SELECT * FROM master..USP_trace_Queue WHERE TraceName = @TraceName)
BEGIN
SELECT @OldQueueHandle = TraceId, @OldTraceFile=TraceFile
FROM master..USP_trace_Queue
WHERE TraceName = @TraceName
IF @@ROWCOUNT<>0 BEGIN
EXEC sp_trace_setstatus @TraceId=@OldQueueHandle, @status=0
EXEC sp_trace_setstatus @TraceId=@OldQueueHandle, @status=2
PRINT 'Deleted trace queue ' + CAST(@OldQueueHandle AS varchar(30))+'.'
PRINT 'The trace output file name is: '+@OldTraceFile+'.trc.'
DELETE master..USP_trace_Queue WHERE TraceName = @TraceName
END
END ELSE PRINT 'No active traces named '+@TraceName+'.'
END ELSE PRINT 'No active traces.'
IF @OnOff='OFF' RETURN 0 -- We've stopped the trace (if it's running), so exit
-- Do some basic param validation
IF (@Cols IS NULL) BEGIN
RAISERROR('You must specify the columns to trace.',16,10)
RETURN -1
END
IF ((@TraceType=0) AND (@Events IS NULL)) BEGIN
RAISERROR('You must specify either @TraceType or @Events.',16,10)
RETURN -1
END
-- Append the datetime to the file name to create a new, unique file name.
IF @file_name IS NULL
begin
SELECT @file_name = 'C:\Temp\Trace\' + @tracename + CONVERT(CHAR(8),getdate(),112) +
REPLACE(CONVERT(varchar(15),getdate(),114),':','')
end
else
begin
--SELECT @file_name = 'F:\Microsoft SQL Server\MSSQL\LOG\Trace\' +@tracename + CONVERT(CHAR(8),getdate(),112) +
SELECT @file_name = @file_name + @tracename + CONVERT(CHAR(8),getdate(),112) +
REPLACE(CONVERT(varchar(15),getdate(),114),':','')
end
-- Delete the file if it exists
DECLARE @cmd varchar(8000)
SET @cmd='DEL '+@file_name
EXEC master..xp_cmdshell @cmd
-- Create the trace queue
EXEC @res=sp_trace_create @TraceId=@QueueHandle OUT, @options=@Options, @tracefile=@file_name,
@maxfilesize=@MaxFileSize, @stoptime=@StopTime
IF @res<>0 BEGIN
IF @res=1 PRINT 'Trace not started. Reason: Unknown error.'
ELSE IF @res=10 PRINT 'Trace not started. Reason: Invalid options. Returned when
options specified are incompatible.'
ELSE IF @res=12 PRINT 'Trace not started. Reason: Error creating file. Returned
if the file already exists, drive is out of space, or path does not exist.'
ELSE IF @res=13 PRINT 'Trace not started. Reason: Out of memory. Returned when
there is not enough memory to perform the specified action.'
ELSE IF @res=14 PRINT 'Trace not started. Reason: Invalid stop time. Returned
when the stop time specified has already happened.'
ELSE IF @res=15 PRINT 'Trace not started. Reason: Invalid parameters. Returned
when the user supplied incompatible parameters.'
RETURN @res
END
PRINT 'Trace started.'
PRINT 'The trace file name is : '+@file_name+'.'+'trc'
select @events = events, @cols = data_columns
from master.dbo.Trace_Scenario
where trace_type = @tracetype
-- Specify the event classes and columns to trace
IF @Events IS NOT NULL BEGIN -- Loop through the @Events and @Cols strings,
--parsing out each event & column number and adding them to the trace definition
IF RIGHT(@Events,1)<>',' SET @Events=@Events+',' -- Append a comma to satisfy the loop
IF RIGHT(@Cols,1)<>',' SET @Cols=@Cols+',' -- Append a comma to satisfy the loop
DECLARE @i int, @j-2 int, @Event int, @Col int, @ColStr varchar(300)
SET @i=CHARINDEX(',',@Events)
WHILE @i<>0 BEGIN
SET @Event=CAST(LEFT(@Events,@i-1) AS int)
SET @ColStr=@Cols
SET @j-2=CHARINDEX(',',@ColStr)
WHILE @j-2<>0 BEGIN
SET @Col=CAST(LEFT(@ColStr,@j-1) AS int)
EXEC sp_trace_setevent @TraceId=@QueueHandle, @eventid=@Event, @columnid=@Col,
@on=@On
SET @ColStr=SUBSTRING(@ColStr,@j+1,300)
SET @j-2=CHARINDEX(',',@ColStr)
END
SET @Events=SUBSTRING(@Events,@i+1,300)
SET @i=CHARINDEX(',',@Events)
END
END
-- Set filters (default values avoid tracing the trace activity itself)
-- Specify other filters like application name etc. by supplying strings to the
--@IncludeTextFilter/@ExcludeTextFilter parameters, separated by semicolons
/*
-- Set the events
--declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 22, 1, @on
exec sp_trace_setevent @TraceID, 22, 6, @on
exec sp_trace_setevent @TraceID, 22, 10, @on
exec sp_trace_setevent @TraceID, 22, 12, @on
exec sp_trace_setevent @TraceID, 22, 14, @on
exec sp_trace_setevent @TraceID, 22, 15, @on
exec sp_trace_setevent @TraceID, 40, 1, @on
exec sp_trace_setevent @TraceID, 40, 6, @on
exec sp_trace_setevent @TraceID, 40, 10, @on
exec sp_trace_setevent @TraceID, 40, 12, @on
exec sp_trace_setevent @TraceID, 40, 14, @on
exec sp_trace_setevent @TraceID, 40, 15, @on
exec sp_trace_setevent @TraceID, 61, 1, @on
exec sp_trace_setevent @TraceID, 61, 6, @on
exec sp_trace_setevent @TraceID, 61, 10, @on
exec sp_trace_setevent @TraceID, 61, 12, @on
exec sp_trace_setevent @TraceID, 61, 14, @on
exec sp_trace_setevent @TraceID, 61, 15, @on
-- Set the Filters
-- Added by BA
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'SET TEXTSIZE%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'SET FMTONLY%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'SELECT%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'set implicit_transaction%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'use %'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'IF %'
set @intfilter = 7
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
exec sp_trace_setfilter @TraceID, 34, 0, 7, N'sort_init'
SET @ExcludeTextFilter = N'SET TEXTSIZE%'
----------------------------------------------------------------------------------------------------------------
*/
SET @OurObjId=OBJECT_ID('master..usp_trace_login_creation')
EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=1, @logical_operator=0,
@comparison_operator=7, @value=N'EXEC% usp_trace_login_creation%'
SET @ExcludeTextFilter = N'SET TEXTSIZE%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'SET FMTONLY%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'set implicit_transaction%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'use %'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'IF %'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'SET QUOTED%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'SET NUMERIC_%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'SET NO_BROWSETABLE%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'select user_name()%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'select usertype,type,name%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'exec sp_MShelpcolumns N%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'%dbo.syscharsets%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'exec sp_help%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'%exec sp_MSdbuseraccess N%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'exec msdb..sp_help%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
--SET @ExcludeTextFilter = N'%dbo.sys%'
--IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
--@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'exec dbo.dt_%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'select count(*) from sysobjects%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'exec sp_MStable%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'set%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'select count(id) from sysobjects%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'select calendar.%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'select count( *) from dbo.calendar%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'exec sp_cursorfetch%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'exec sp_unprepare%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'dbcc dbreindex(N%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'SELECT%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'exec sp_cursorclose%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'exec sp_cursoroption%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'%SELECT security_info.window , security_info.control , security_info.status%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'%SELECT calendar.absolute_date FROM calendar WHERE calendar.hijera_date =@P1%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @ExcludeTextFilter = N'EXECUTE%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=7, @value=@ExcludeTextFilter
SET @IncludeTextFilter = N'sp_revokelogin%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=1, @comparison_operator=6, @value=@IncludeTextFilter
SET @IncludeTextFilter = N'sp_grantlogin%'
IF @ExcludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=1, @comparison_operator=6, @value=@IncludeTextFilter
exec sp_trace_setfilter @QueueHandle, 1, 1, 6, N'exec sp_revokelogin%'
exec sp_trace_setfilter @QueueHandle, 1, 1, 6, N'exec sp_grantlogin%'
SET @IncludeObjIdFilter = 7274611
IF @IncludeObjIdFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=22, @logical_operator=0, @comparison_operator=0, @value=@IncludeObjIdFilter
Declare @intfilter int
set @intfilter = 1
set @TraceId=@QueueHandle
--exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter
--exec sp_trace_setfilter @TraceID, 34, 0, 7, N'sort_init'
------------------------------------------------------------------------------
IF @IncludeTextFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=1, @logical_operator=0, @comparison_operator=6, @value=@IncludeTextFilter
IF @IncludeObjIdFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=22, @logical_operator=0, @comparison_operator=0, @value=@IncludeObjIdFilter
EXEC sp_trace_setfilter @TraceId=@QueueHandle, @columnid=22, @logical_operator=0,
@comparison_operator=1, @value=@OurObjId
IF @ExcludeObjIdFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=22, @logical_operator=0, @comparison_operator=1, @value=@ExcludeObjIdFilter
IF @IncludeObjNameFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=34, @logical_operator=0, @comparison_operator=6, @value=@IncludeObjNameFilter
EXEC sp_trace_setfilter @TraceID=@QueueHandle,@columnid=3, @logical_operator=1,
@comparison_operator=0, @value=@intfilter
SET @ExcludeObjNameFilter = N'sort_init'
IF @ExcludeObjNameFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=34, @logical_operator=0, @comparison_operator=7, @value=@ExcludeObjNameFilter
IF @IncludeHostFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=8, @logical_operator=0, @comparison_operator=6, @value=@IncludeHostFilter
IF @ExcludeHostFilter IS NOT NULL EXEC sp_trace_setfilter @TraceId=@QueueHandle,
@columnid=8, @logical_operator=0, @comparison_operator=7, @value=@ExcludeHostFilter
----------------------------------------------------------------------------------------
set @intfilter = 7274611
exec sp_trace_setfilter @TraceID, 22, 1, 0, @intfilter
----------------------------------------------------------------------------------------
-- Turn the trace on
EXEC sp_trace_setstatus @TraceId=@QueueHandle, @status=1
-- Record the trace queue handle for subsequent jobs. (This allows us to know
--how to stop our trace.)
IF OBJECT_ID('master..USP_trace_Queue') IS NULL BEGIN
CREATE TABLE master..USP_trace_Queue (TraceId int, TraceName varchar(40),
TraceFile sysname)
INSERT master..USP_trace_Queue VALUES(@QueueHandle, @TraceName, @file_name)
END ELSE BEGIN
IF EXISTS(SELECT 1 FROM master..USP_trace_Queue WHERE TraceName = @TraceName)
BEGIN
UPDATE master..USP_trace_Queue SET TraceId = @QueueHandle, TraceFile=@file_name
WHERE TraceName = @TraceName
END ELSE BEGIN
INSERT master..USP_trace_Queue VALUES(@QueueHandle, @TraceName, @file_name)
END
END
RETURN 0
Help:
PRINT 'USP_TRACE_INFO -- Starts/stops a Profiler-like trace using Transact-SQL
server side stored procedures.'
DECLARE @crlf char(2), @tabc char(1)
SET @crlf=char(13)+char(10)
SET @tabc=char(9)
PRINT @crlf+'Parameters:'
PRINT @crlf+@tabc+'@OnOff varchar(3) default: /? -- Help'
PRINT @crlf+@tabc+'@file_name sysname default: c:\temp\YYYYMMDDhhmissmmm.trc --
Specifies the trace file name (SQL Server always appends .trc extension)'
PRINT @crlf+@tabc+'@TraceName sysname default: tsqltrace -- Specifies the name
of the trace'
PRINT @crlf+@tabc+'@TraceType int default: 0 -- Specifies the type of trace to
run (obtained from the Trace table: master.dbo.Trace_Scenario)'
PRINT @crlf+@tabc+'@Options int default: 2 (TRACE_FILE_ROLLOVER)'
PRINT @crlf+@tabc+'@MaxFileSize bigint default: 4000 MB'
PRINT @crlf+@tabc+'@StopTime datetime default: NULL'
PRINT @crlf+@tabc+'@Events varchar(300) default: SP-related events and
errors/warnings -- Comma-delimited list specifying the events numbers to trace.
(Obtained from the Trace table: master.dbo.Trace_Scenario)'
PRINT @crlf+@tabc+'@Cols varchar(300) default: All columns -- Comma-delimited
list specifying the column numbers to trace. (obtained from the Trace table:
master.dbo.Trace_Scenario)'
PRINT @crlf+@tabc+'@IncludeTextFilter sysname default: NULL -- String mask
specifying what TextData strings to include in the trace'
PRINT @crlf+@tabc+'@ExcludeTextFilter sysname default: NULL -- String mask
specifying what TextData strings to filter out of the trace'
PRINT @crlf+@tabc+'@IncludeObjIdFilter sysname default: NULL -- Specifies the id
of an object to target with the trace'
PRINT @crlf+@tabc+'@ExcludeObjIdFilter sysname default: NULL -- Specifies the id
of an object to exclude from the trace'
PRINT @crlf+@tabc+'@TraceId int default: NULL -- Specified the id of the trace
to list when you specify the LIST option to @OnOff'
PRINT @crlf+'Examples: '
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO -- Displays this help text'
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO ''ON'' -- Starts a trace'
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO ''OFF'' -- Stops a trace'
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO ''ON'', @file_name=''E:\log\mytrace'' --
Starts a trace with the specified file name'
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO ''ON'',@Events=''37,43'' -- Starts a
trace the traps the specified event classes'
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO ''ON'',@Cols=''1,2,3'' -- Starts a trace
that includes the specified columns'
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO ''ON'',@IncludeTextFilter=''EXEC% FooProc%''
-- Starts a trace that includes events matching the specified TextData mask'
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO ''ON'',@tracename=''Receiving_50_Ctns''
-- Starts a trace using the specified name'
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO ''OFF'',@tracename=''Receiving_50_Ctns''
-- Stops a trace with the specified name'
PRINT @crlf+@tabc+'EXEC USP_TRACE_INFO ''ON'',@file_name = ''E:\log\mytrace'',
-- Starts a trace with the specified parameters'
PRINT @tabc+@tabc+'@TraceName = ''Receiving_50_Ctns'','
PRINT @tabc+@tabc+'@Options = 2, '
PRINT @tabc+@tabc+'@TraceType = 0,'
PRINT @tabc+@tabc+'@MaxFileSize = 500,'
PRINT @tabc+@tabc+'@StopTime = NULL, '
PRINT @tabc+@tabc+'@Events =
''10,11,14,15,16,17,27,37,40,41,55,58,67,69,79,80,98'','
PRINT @tabc+@tabc+'@Cols = DEFAULT,'
PRINT @tabc+@tabc+'@IncludeTextFilter = NULL,'
PRINT @tabc+@tabc+'@IncludeObjIdFilter = NULL,'
PRINT @tabc+@tabc+'@ExcludeObjIdFilter = NULL'
PRINT @crlf+@tabc+'To list all the traces currently running:'
PRINT @crlf+@tabc+@tabc+'USP_TRACE_INFO ''LIST'''
PRINT @crlf+@tabc+'To list information about a particular trace:'
PRINT @crlf+@tabc+@tabc+'USP_TRACE_INFO ''LIST'', @TraceId=n -- where n is the
trace ID you want to list'
PRINT @crlf+@tabc+'To stop a specific trace, supply the @TraceName parameter
when you call USP_TRACE_INFO ''OFF''.'
RETURN 0
SET NOCOUNT OFF
END