December 3, 2008 at 1:35 am
I get below message from my SQL scheduled job when I run
Job Name :Manage Login Creation Trace
job body:
((EXEC usp_trace_login_creation 'OFF',@tracename='Login Creation on SQL-Server1 '
EXEC usp_trace_login_creation 'ON',@file_name ='F:\Microsoft SQL Server\MSSQL\Audit\',
@TraceName = 'Login Creation on SQL-Server1 ',
@Options = 2,
@TraceType = 8,
@MaxFileSize = 500,
@StopTime = NULL,
@Events = DEFAULT,
@Cols = DEFAULT,
@IncludeTextFilter = NULL,
@IncludeObjIdFilter = 7274611,
@ExcludeObjIdFilter = NULL))
----------------------------------------------------------------------------------------
Fail Message:
Executed as user: SIDF\Administrator. Deleted trace queue 2. [SQLSTATE 01000] (Message 0) The trace output file name is: F:\Microsoft SQL Server\MSSQL\Audit\Login Creation on SQL-Server1 20081120233001280.trc. [SQLSTATE 01000] (Message 0) No active traces named Login Creation on SQL-Server1 . [SQLSTATE 01000] (Message 0) Filters with the same event column ID must be grouped together. [SQLSTATE 42000] (Error 19055) Trace started. [SQLSTATE 01000] (Error 0) The trace file name is : F:\Microsoft SQL Server\MSSQL\Audit\Login Creation on SQL-Server1 20081121233000543.trc [SQLSTATE 01000] (Error 0). The step failed.
------------------------------------------------------------------
In sql server2000 it was run successfully but now with sql2005 it’s failed but file is created fine.
Could you please help me to solve this issue because every day I got e-mail (status: fail)?
Not: This script I have downloaded from this site.
December 3, 2008 at 6:46 am
Can you post the code for the stored procedure?
Have you tried running the code within the stored procedure outside the stored procedure to see what line is producing the error?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2008 at 3:44 am
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
December 21, 2008 at 1:41 am
I ran code on Query Analyzer and I got theses messages
Messages:
Deleted trace queue 4.
The trace output file name is: F:\Microsoft SQL Server\MSSQL\Audit\Login Creation on SQL-Server1 20081220233000503.trc.
No active traces named Login Creation on SQL-Server1 .
Trace started.
The trace file name is : F:\Microsoft SQL Server\MSSQL\Audit\Login Creation on SQL-Server1 20081221105557597.trc
Msg 19055, Level 16, State 1, Procedure sp_trace_setfilter, Line 1
Filters with the same event column ID must be grouped together.
Msg 19055, Level 16, State 1, Procedure sp_trace_setfilter, Line 1
Filters with the same event column ID must be grouped together
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply