Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2008
    Posts
    4

    Unanswered: Part of sp_trace_setfilter not working

    Hi all, I've referred to your wonderful forum for help in the past, but the time has come alas to post a question of my own since no one else seems to be willing to help out (gotta love finger pointing).

    We're using a product, CA Audit, that set up a job to create trace files and load data from the trc files into a table, and a table to keep track of the files loaded.

    Most of the filters I've created are working fine, which are filtering on columns 34 and 35.
    Now I've been trying to filter based on the Permissions column, 19, but SQL gives errors when I run the sp with that.
    What I am trying to accomplish, is to NOT trace events with Permissions value of 1 (SELECT ALL).

    Here's the code - NOTE: I have obscured the columns being traced as well as the return fields except the 3 pertinent to this for security reasons, just to be extra safe.

    All of the setevents for each column being pulled have all of the same fields being returned.
    The sp is fed the 3 values requested, the first being the directory to put the file in, the second being 125 and the third being 1 2 or 3 typically.

    Code:
    USE [IRECORDER]
    GO
    /****** Object:  StoredProcedure [dbo].[dba_Trc_eTrust_Audit]    Script Date: 07/15/2008 11:33:04 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ALTER PROCEDURE [dbo].[dba_Trc_eTrust_Audit]
        @FileBase nvarchar(1000)
      , @Sec int
      , @TraceID int OUTPUT -- Return the TraceID to the caller.
      WITH RECOMPILE
    /*
    * Creates a trace 
    ***********************************************************************/
    AS 
    -- Create a Queue
    declare @rc int
    declare @dbaTraceID int
    declare @maxfilesize bigint
    declare @DateTime datetime
    declare @TraceCount int
    DECLARE @FileName nvarchar(1000) 
          , @StartDT  datetime -- When the trace started
    
    SELECT @StartDT = getdate()
    
    set @TraceCount = (SELECT COUNT(*) FROM dbo.dbaTrace WHERE FileLoadedDT is NULL)
    if (@TraceCount >= 10) 
    BEGIN
    	PRINT 'More Trace Files'
    	goto finish
    END
    
    set @maxfilesize = 50 
    
    SELECT @FileName = @FileBase
    	+ convert(varchar, @StartDT, 12)
    	+ replace(convert(varchar, GetDate(), 8), ':', '')
    
         , @DateTime = DATEADD (s, @Sec, @StartDT)
         
    set @maxfilesize = 50
    exec @rc = sp_trace_create @TraceID output, 2, @FileName, @maxfilesize, @DateTime
    
    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, x, y, @on
    exec sp_trace_setevent @TraceID, x, 19, @on
    exec sp_trace_setevent @TraceID, x, y, @on
    exec sp_trace_setevent @TraceID, x, 34, @on
    exec sp_trace_setevent @TraceID, x, 35, @on
    exec sp_trace_setevent @TraceID, x, y, @on
    
    
    
    -- Set up the trace filters
    
    exec sp_trace_setfilter @TraceID, 19, 0, 2, N'1'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'SEOSDATA'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'dbaTrace'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'dbaTraceDetail'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'dbaTraceMissing'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'sp_trace_create'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'sp_trace_setstatus'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'sp_trace_setfilter'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'db_Trc_Load'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'db_Trc_LoadFile'
    exec sp_trace_setfilter @TraceID, 34, 0, 7, N'db_Trc_eTrust_Audit'
    exec sp_trace_setfilter @TraceID, 35, 0, 7, N'IRECORDER'
    exec sp_trace_setfilter @TraceID, 35, 0, 7, N'tempdb'
    exec sp_trace_setfilter @TraceID, 35, 0, 7, N'msdb'
    exec sp_trace_setfilter @TraceID, 35, 0, 7, N'master'
    exec sp_trace_setfilter @TraceID, 35, 0, 7, N'mssqlsystemresource'
    exec sp_trace_setfilter @TraceID, 35, 0, 7, N'ReportServer'
    exec sp_trace_setfilter @TraceID, 35, 0, 7, N'ReportServerTempDB'
    
    
    -- Set the trace status to start
    exec sp_trace_setstatus @TraceID, 1
    
    -- display trace id for future references
    
    EXEC @rc = dba_Trc_Record @FileName, @StartDT, @Sec
                    , 'Scripted Trace for etrust Audit'
                    , @TraceID, @dbaTraceID OUTPUT
    
    PRINT 'Recording Started.  SQL Trace ID=' 
                              + CONVERT(varchar(9), @TraceID)
    PRINT 'dbaTrace.dbaTraceID = ' 
                              + CONVERT(varchar(9), @dbaTraceID)
    goto finish
    
    error: 
    select ErrorCode=@rc
    
    finish:
    Without the line "exec sp_trace_setfilter @TraceID, 19, 0, 2, N'1'" the trace works fine.
    I know that for column 19, you can only use the comparison operators of = (1), <> (2), >= (4) or <= (5).
    I have tried it with all 4 of the operators.
    When using 4 and 5, I obviously did it with the number above it, so N'2'.

    For testing, I've tried to get it to both NOT capture permission of 1, and also TO capture permission of 1. Just for the sake of testing.

    For any of the variants I've tried in testing, if I manually execute it and fill in the values, I get back the following error:
    Msg 214, Level 16, State 3, Procedure sp_trace_setfilter, Line 1
    Procedure expects parameter '@value' of type 'bigint'.


    When it tries running on it's own through the job, it reports:

    Date 7/15/2008 11:51:55 AM
    Log Job History (eTrust_Audit_Job)

    Step ID 1
    Server (hostname)
    Job Name eTrust_Audit_Job
    Step Name Create_Trace
    Duration 00:00:00
    Sql Severity 16
    Sql Message ID 214
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: NT AUTHORITY\SYSTEM. Procedure expects parameter '@value' of type 'bigint'. [SQLSTATE 42000] (Error 214). The step failed.


    What I don't understand is how the one trace line would cause that to happen.

    The only thing defined as bigint in the sp is "declare @maxfilesize bigint", which is being defined as 50 in the sp. That doesn't change any.... and there are less than 50 files in the directory.

    Any help, pointers, etc much appreciated!

    Regards,
    okaasama

  2. #2
    Join Date
    Jul 2008
    Posts
    4

    tried this... didn't work

    At someone's suggestion elsewhere, I tried the filter line like this:

    exec sp_trace_setfilter @TraceID, 19, 0, 1, 1

    But it had the same result.
    Any suggestions welcomed!

    Thanks,
    okaasama

  3. #3
    Join Date
    Jul 2008
    Posts
    4
    SOLVED!

    I had to change it to:
    Code:
    declare @bigintfilter bigint
    
    set @bigintfilter = 1
    exec sp_trace_setfilter @TraceID, 19, 0, 1, @bigintfilter
    Any idea why it wouldn't work just as the following?
    Code:
    exec sp_trace_setfilter @TraceID, 19, 0, 1, 1
    I'd like to understand.

    Thanks,
    okaasama

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Implicit data type conversion techniques in extended stored procedures are not defined, described, explained, or even documented. When you were passing 1, it was accepted as INT. I don't know what the C/C++ code inside does to it, including possibly interpreting it as 0 or null. With procedures like this you need to be very specific in terms of data type-ing, to avoid what you just experienced.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to second Robert's point. It is not just Bigints - I was playing with some code from a blog re traces recently:
    http://blogs.msdn.com/sqlqueryproces...-analysis.aspx

    If y0ou run their code - same problem except that the exec sp_trace_setevent expects a bit parameter (@on). Same solution.

    In case you haven't seen it, this is a nice article on server side tracing and I think (not read for a while) he mentions this problem - or at least accounts for it.
    http://vyaskn.tripod.com/server_side...sql_server.htm
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2008
    Posts
    4

    Thanks!

    Thanks for the advice, I really appreciate it.

    I was googling and googling until my fingers were numb, searching through microsoft, I couldn't find ANYTHING. It's astounding to me.

    But then again, I suppose I've seen other ridiculousness like that with other technologies.

    Again, many thanks!

    Regards,
    okaasama

  7. #7
    Join Date
    Oct 2008
    Posts
    1

    sp_trace_setevent not working

    Hey Okaasama

    I had the same problem! Like you say - "astounding". I finally found an answer, I think it was at sqltips.com. Basically declare @On as a BIT variable then set it to 1 to set the trace_events that you want on. So:
    DECLARE @On BIT
    SET @On = 1
    exec sp_trace_setfilter @TraceID, 19, 0, 1, @On

    Tada!

    You can buy me a malt Beerrrr when you're in the 'hood.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •