Trace issue when using sp_trace_setfilter

• Peter D-251663

  Ten Centuries

  Points: 1010

  More actions

  May 29, 2011 at 1:00 pm

  #99304

  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

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  May 29, 2011 at 5:16 pm

  #1331483

  You cannot set filters on a trace you haven't created yet...move the filter setting until after the sp_trace_create.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]