Trace issue when using sp_trace_setfilter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Trace issue when using sp_trace_setfilter

Author

Topic

netwerkassist
Starting Member

13 Posts

Posted - 2011-05-29 : 14:58:24

I have a stored procedure that runs on SQL 2000 that captures tracing events. It fails to run when I insert a sp_trace_filter, with the error message "Procedure expects parameter '@traceid' of type 'int' ".
It runs fine without the trace filter.

Looking for insight as the traceid variable has been declared.

Here is the SP:

CREATE Procedure usp_TraceForMinutes
@FilePath nvarchar(255)='F:\MSSQL\SQLTRACE2\fscmtrace',
@NumOfFiles int = 8,
@MinutesToRun int = 5,
@MaxFileSize bigint = 600
As

--Purpose: Runs a trace to file for a specified duration in minutes and rotates the files.
--Usage: To use this script:
-- modify variables in the 'Configuration Parameters' Section
-- and Add and Remove Columns in the 'Assign Columsn to Display' Section
-- and Add and Remove Events in the 'Assign Events to Display' Section

--Declare Variables
declare @CommandString nvarchar(255),@Counter int,
@on bit,@outputfile nvarchar(128),@stoptime datetime,@TraceColumnNum int,@TraceColumnsCounter int,
@TraceEventNum int,@TraceEventsCounter int,@TraceID int

--End Configuration Parameters

--Start Prepare Variables and TempTables
set @on=1
set @outputfile=@FilePath + '1'
set @stoptime = dateadd(mi,@MinutesToRun,getdate())
set @TraceID=null

declare @TraceColumns TABLE
(
ColumnUID int IDENTITY(1,1),
ColumnNum int
)

declare @TraceEvents TABLE
(
EventUID int IDENTITY(1,1),
EventNum int
)
--End Prepare Vairables and TempTables

--Assign Columns to Display - ADD OR REMOVE COLUMNS TO DISPLAY HERE
--A list of all possible columns can be found at http://msdn2.microsoft.com/en-us/library/ms186265.aspx
Insert @TraceColumns (ColumnNum) values (1)--TextData
Insert @TraceColumns (ColumnNum) values (3)--DatabaseID
Insert @TraceColumns (ColumnNum) values (4)--TransactionID
Insert @TraceColumns (ColumnNum) values (8)--ClientHostName
Insert @TraceColumns (ColumnNum) values (11)--Login Name
Insert @TraceColumns (ColumnNum) values (12)--SPID
Insert @TraceColumns (ColumnNum) values (13)--Duration
Insert @TraceColumns (ColumnNum) values (14)--StartTime
Insert @TraceColumns (ColumnNum) values (15)--EndTime
Insert @TraceColumns (ColumnNum) values (16)--Reads
Insert @TraceColumns (ColumnNum) values (17)--Writes
Insert @TraceColumns (ColumnNum) values (20)--Severity
Insert @TraceColumns (ColumnNum) values (21)--EventSubClass
Insert @TraceColumns (ColumnNum) values (22)--ObjectID
Insert @TraceColumns (ColumnNum) values (24)--IndexID
Insert @TraceColumns (ColumnNum) values (25)--IntegerData
Insert @TraceColumns (ColumnNum) values (27)--EventClass
Insert @TraceColumns (ColumnNum) values (32)--Mode

--Assign Events to Display - ADD OR REMOVE EVENTS TO DISPLAY HERE
--A list of all possible events can be found at http://msdn2.microsoft.com/en-us/library/ms186265.aspx
Insert @TraceEvents (EventNum) Values (10)--10 RPC:Completed - Occurs when a remote procedure call (RPC) has completed.
Insert @TraceEvents (EventNum) Values (11)--11 RPC:Starting - Occurs when an RPC has started.
Insert @TraceEvents (EventNum) Values (12)--12 SQL:BatchCompleted - Occurs when a Transact-SQL batch has completed.
Insert @TraceEvents (EventNum) Values (13)--13 SQL:BatchStarting - Occurs when a Transact-SQL batch has started.
Insert @TraceEvents (EventNum) Values (16)--16 Attention - Occurs when attention events, such as client-interrupt requests or broken client connections, happen.
Insert @TraceEvents (EventNum) Values (25)--25 Lock:Deadlock - Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources the other transaction owns.
Insert @TraceEvents (EventNum) Values (33)--33 Exception - Indicates that an exception has occurred in SQL Server.
Insert @TraceEvents (EventNum) Values (40)--40 SQL:StmtStarting - Occurs when the Transact-SQL statement has started.
Insert @TraceEvents (EventNum) Values (41)--41 SQL:StmtCompleted - Occurs when the Transact-SQL statement has completed.
Insert @TraceEvents (EventNum) Values (44)--44 SP:StmtStarting - Indicates that a Transact-SQL statement within a stored procedure has started executing.
Insert @TraceEvents (EventNum) Values (45)--45 SP:StmtCompleted - Indicates that a Transact-SQL statement within a stored procedure has finished executing.
Insert @TraceEvents (EventNum) Values (50)--50 SQL Transaction - Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK TRANSACTION statements.
Insert @TraceEvents (EventNum) Values (59)--59 Lock:Deadlock Chain - Produced for each of the events leading up to the deadlock.
Insert @TraceEvents (EventNum) Values (70)--70 CursorPrepare - Indicates when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE DB, or DB-Library.
Insert @TraceEvents (EventNum) Values (74)--74 CursorExecute - A cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or DB-Library is executed.

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 6, 0, 7, N'pssql'

--Start File Movement Section

--Delete the Oldest File
set @CommandString = 'del ' + @FilePath + Cast(@NumOfFiles as nvarchar(4)) + '.trc'
exec xp_cmdshell @CommandString

--Move the file# on each file up by one
set @Counter = @NumOfFiles
While @Counter > 1
Begin
set @CommandString = 'move ' + @FilePath + Cast(@Counter-1 as nvarchar(4))+ '.trc ' + @FilePath + Cast(@Counter as nvarchar(4))+'.trc'
exec xp_cmdshell @CommandString
Set @Counter = @Counter - 1
End
--End File Movement Section

--Create the trace
exec sp_trace_create @TraceID output, 0, @outputfile, @MaxFileSize, @stoptime

--Add Trace Events

Select @TraceEventsCounter = min(EventUID) from @TraceEvents

While @TraceEventsCounter is not null
Begin
select @TraceEventNum = EventNum from @TraceEvents Where EventUID = @TraceEventsCounter
select @TraceColumnsCounter = min(ColumnUID) from @TraceColumns
While @TraceColumnsCounter is not null
Begin
select @TraceColumnNum = ColumnNum from @TraceColumns where ColumnUID = @TraceColumnsCounter
exec sp_trace_setevent @TraceID,@TraceEventNum,@TraceColumnNum,@on
select @TraceColumnsCounter = min(ColumnUID) from @TraceColumns where ColumnUID > @TraceColumnsCounter
END
select @TraceEventsCounter = min(EventUID) from @TraceEvents where EventUID > @TraceEventsCounter
End

--Start the trace
exec sp_trace_setstatus @TraceID, 1
GO

Appreciate insight

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-29 : 15:49:43

You need to run sp_trace_setfilter AFTER sp_trace_create, and you'll need to capture the TraceId via the output parameter for sp_trace_create. Use that TraceId for all of the next stored procedures you have to run. It all starts with sp_trace_create.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources