Profiler trace to table

Question

Profiler trace to table

simon.letts

Hall of Fame

Points: 3876
More actions
January 23, 2008 at 6:17 am

#119718

Hi, I run a lot of traces and have set them up both via t-sql and using the Profiler.
However what I need to do is have a trace run automatically ie via an agent scheduled job each day or even permanently. Trouble is I want it to write to a table so I can place a trigger on the table whenever anything is written to it (dont worry the trace file will be timy and in theory should never pick anything up!) However when looking at sp_trace_create I can only use that to write to a file, which I could work with another way if it was ASCII, but it has to be a .trc file and can only be opened with Profiler !!
Any thoughts as to how I could work this please !?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

simon.letts (1/23/2008)
Hi, I run a lot of traces and have set them up both via t-sql and using the Profiler.
However what I need to do is have a trace run automatically ie via an agent scheduled job each day or even permanently. Trouble is I want it to write to a table so I can place a trigger on the table whenever anything is written to it (dont worry the trace file will be timy and in theory should never pick anything up!) However when looking at sp_trace_create I can only use that to write to a file, which I could work with another way if it was ASCII, but it has to be a .trc file and can only be opened with Profiler !!
Any thoughts as to how I could work this please !?

You cannot set the target of the trace to be a table, but you could read the trace file in into a table (and on 2005 the trace can continue to run at the same time). For this see fn_trace_gettable. Not perfect, because you would end up polling.

What are you trying to achieve with some of these traces? Maybe a ddl trigger on event notification could do the same?

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

I tend to agree with Andras that there may be a better way to do this other than a trace.

simon.letts Hall of Fame Points: 3876 More actions · Answer 3

Thanks guys. Yes basically I just need to see when a specific table has been accessed via a select (or other) statement. Could you please give me an example of this trigger type.

thanks again.