Re: Creating a trace from a script

Bill E. (billmiami2@xxxxxxxxxxxx) writes:
I do this all the time on SQL 2005 but for some reason it's not
working properly on SQL 2000.

I'm trying to create a trace from a script (sp_trace_create,
sp_trace_setevent, sp_trace_setfilter, sp_trace_setstatus). I'm
saving the output to a .trc file. When I run my script, the trace
file is created on the server, but its size is 0 bytes and it does not
grow as expected. When I try to read from it with a SELECT on
fn_trace_gettable, I get an error saying that my trace file is
unreadable.

I am able to create a working trace using Profiler but when I script
the trace and then run the script, the same thing happens.

What could be the problem? Is it a permissions issue?

Did you stop the trace before you tried to access it?


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: User and Login auditing
    ... I am running SQL 7. ... You could put triggers on your trace table. ... How to Trace in SQL Server by Using Extended Stored ...
    (microsoft.public.sqlserver.security)
  • Re: Tracking user activities
    ... >> I'd like these settings to persist permanently and the event records to ... > In SQL 2000, this is not very simple to do. ... > Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... select Script Trace. ...
    (comp.databases.ms-sqlserver)
  • Re: C2 SQL auditing
    ... Andrew J. Kelly SQL MVP ... Do you suggest any third party tools for SOX compliance? ... I would like to off load the server side trace to the SOX team ... when I stop SQL server then my trace file gets populated. ...
    (microsoft.public.sqlserver.security)
  • Re: How to use xp_trace_generate_event
    ... Below is the script I am using: ... declare @QueueHandle int ... >>I have created a stored procedure that create a trace on SQL 7 and start ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL logins logged where
    ... you can't trace directly to a table but can use the> fn_trace_gettable function to load the trace file into a> table. ... If you are using SQL Server 7, use> xp_trace_setqueuedestination to set the destination to a> table. ... Security Tab does not give too many ...
    (microsoft.public.sqlserver.security)