Hi,

I want to automate tracing for SQL server Profiler. I have written a set of
stored procedures using the SQL Server in-built sp's for the very purpose.

A trace is also being successfully created with trace id and a
file(MyTrace.trc) is also created however the trace is not writing anything
into the file MyTrace.trc

I've started the trace using the sp sp_trace_setstatus @TraceId, 1

and when accessing info of the trace using
SELECT *
FROM ::fn_trace_getinfo(@TraceId)

it shows the correct file path and status as 1

Could some please help me out in this.

What else am i required to do for enabling server side trace and so that the
capture sql traces are written into the file.

Regards
Shajee

The easiest way to make sure you're on the right track is to use the
Profiler tool, get a trace running that is capturing what you want, stop it
and the script it (File>Script Trace). Then run the generated script and
stop it and check you have results. If this is all working okay then check
your code and make sure you don't have any filters present that might be
interfering with the capture. In SQL2000 you need to both stop the
serverside trace and delete its definition from the server in order to open
it correctly using Profiler or fn_trace_gettable. In SQL2005 this is no
lonfer the case and you can access a running server side trace. Are you
actually getting an error or is it simply that the trace file is empty. If
it is truly empty then it's likely an issue with your code - double check it
against the script generated by Profiler

Show quoteHide quote

Thanks for the help. I really appreciate that.

The problem is resolved. Actually i was stopping the trace but not
closing it. So the captured events were not being written into
file(.trc). I'm using SQL 2000

Now after deleting the definition of trace by using

sp_trace_setstatus @TraceId,2 it works and the specified file gets
update which's then accessed by function

Thanks for the help once again :)

Regards
Shajee

*** Sent via Developersdex http://www.developersdex.com ***

Writes to the trace file happen in 128K chunks. Are you sure you've
generated that many events?
Try stopping and closing the trace (this takes to calls to
sp_trace_setstatus, first stop and then close the trace.
Or try generating lots of events to get 128K worth of data generated.

Show quoteHide quote

Was 'Create Stored Procedure' wizard droped from SQL Server 2005
DELETING 100 million from a table weekly SQl SERVER 2000
SQL Server 2005 & Visual Studio 205 RC
Management Studio - Open File
Filtering out blank TextData in Profiler